Lending Club Case Study

Group Members

1. Chethana manyam - chethana.manyam@sap.com
2. Jenifer Sam - jenifer.sam@sap.com
3. Pallavi Nigam - pallavi.nigam@sap.com

Description

Lending Club is a marketplace for personal loans that matches borrowers who are seeking a loan with investors looking to lend money and make a return.

When the company receives a loan application, the company has to make a decision for loan approval based on the applicant’s profile. Two types of risks are associated with the bank’s decision:

• If the applicant is likely to repay the loan, then not approving the loan results in a loss of business to the company
• If the applicant is not likely to repay the loan, i.e. he/she is likely to default, then approving the loan may lead to a financial loss for the company

Business Objective

The dataset we have been given contains the information about past loan applicants and whether they ‘defaulted’ or not. The aim is to identify patterns which indicate if a person is likely to default, which may be used for taking actions such as denying the loan, reducing the amount of loan, lending (to risky applicants) at a higher interest rate, etc.

In this case study, we will use Exploratory Data Analysis to understand how consumer attributes and loan attributes influence the tendency of default.

Technical Objective

To use Exploratory Data Analysis (EDA) to understand how consumer attributes and loan attributes influence the tendency of default. From the insights identified, we will provide recommendations on what attributes can be considered to reduce loans that result in credit loss.

Steps Followed in our EDA

1. Data Sourcing
2. Data Cleanup
3. Data Understanding
4. Univariate Analysis
5. Bivariate Analysis (Includes analysis using Derived Metrics)
6. Multivariate Analysis

1. Data Sourcing

Load the given dataset

# Import header files
import numpy as np
import pandas as pd
import missingno as msno
import matplotlib.pyplot as plt
import seaborn as sns
import plotly
import plotly.graph_objects as go
from plotly.subplots import make_subplots

# Load data
loan = pd.read_csv("loan.csv", low_memory=False)

'loan' is the master dataset.

#Get an overview of the structure of the data
loan.head()

	id	member_id	loan_amnt	funded_amnt	funded_amnt_inv	term	int_rate	installment	grade	sub_grade	...	num_tl_90g_dpd_24m	num_tl_op_past_12m	pct_tl_nvr_dlq	percent_bc_gt_75	pub_rec_bankruptcies	tax_liens	tot_hi_cred_lim	total_bal_ex_mort	total_bc_limit	total_il_high_credit_limit
0	1077501	1296599	5000	5000	4975.0	36 months	10.65%	162.87	B	B2	...	NaN	NaN	NaN	NaN	0.0	0.0	NaN	NaN	NaN	NaN
1	1077430	1314167	2500	2500	2500.0	60 months	15.27%	59.83	C	C4	...	NaN	NaN	NaN	NaN	0.0	0.0	NaN	NaN	NaN	NaN
2	1077175	1313524	2400	2400	2400.0	36 months	15.96%	84.33	C	C5	...	NaN	NaN	NaN	NaN	0.0	0.0	NaN	NaN	NaN	NaN
3	1076863	1277178	10000	10000	10000.0	36 months	13.49%	339.31	C	C1	...	NaN	NaN	NaN	NaN	0.0	0.0	NaN	NaN	NaN	NaN
4	1075358	1311748	3000	3000	3000.0	60 months	12.69%	67.79	B	B5	...	NaN	NaN	NaN	NaN	0.0	0.0	NaN	NaN	NaN	NaN

5 rows × 111 columns

#Print number of rows in the dataset
print(len(loan))

39717

#Identify structure of dataset
loan.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 39717 entries, 0 to 39716
Columns: 111 entries, id to total_il_high_credit_limit
dtypes: float64(74), int64(13), object(24)
memory usage: 33.6+ MB

We have 39717 rows and 111 columns of data in the original dataset

2. Data Cleanup: Column-wise

A. Removing customer variables

Customer variables are those variables that are generated after a loan is approved. These metrics will not be used to determine the outcome of this case study. Therefore, these columns can be dropped.

• application_type
• collection_recovery_fee
• delinq_2yrs
• earliest_cr_line
• inq_last_6mths
• last_credit_pull_d
• last_pymnt_amnt
• last_pymnt_d
• open_acc
• out_prncp
• out_prncp_inv
• pub_rec
• recoveries
• revol_bal
• revol_util
• total_acc
• total_pymnt
• total_pymnt_inv
• total_rec_int
• total_rec_late_fee
• total_rec_prncp
• url

cust_var_col = ['application_type',
                'collection_recovery_fee',
                'delinq_2yrs',
                'earliest_cr_line',
                'inq_last_6mths',
                'last_credit_pull_d',
                'last_pymnt_amnt',
                'last_pymnt_d',
                'open_acc',
                'out_prncp',
                'out_prncp_inv',
                'pub_rec',
                'recoveries',
                'revol_bal',
                'revol_util',
                'total_acc',
                'total_pymnt',
                'total_pymnt_inv',
                'total_rec_int',
                'total_rec_late_fee',
                'total_rec_prncp',
                'url']
loan = loan.drop(cust_var_col, axis=1)

loan.shape

(39717, 89)

B. Removing columns where all values are null

loan = loan.dropna(axis=1, how='all')
loan.shape

(39717, 35)

C. Using missingno library, identify columns with more tha 50% missing values

msno.bar(loan)

<Axes: >

Our aim is to remove those columns with more than 50% missing values. From the above chart, it is clear that the following columns have more than 50% missing values and therefore, can be removed.

• mths_since_last_delinq
• mths_since_last_record
• next_pymnt_d

col_to_drop = ['mths_since_last_delinq',
               'mths_since_last_record',
               'next_pymnt_d']
loan = loan.drop(col_to_drop, axis = 1)
loan.shape

(39717, 32)

D. Remove columns with just one value - These won't impact the analysis

By skimming through the dataset, it looks like some columns have just one value or a combination of NA and 0s. Such columns will not affect our analysis. Let's remove them after a proper analysis of number of unique values in each column.

loan.nunique()

id                            39717
member_id                     39717
loan_amnt                       885
funded_amnt                    1041
funded_amnt_inv                8205
term                              2
int_rate                        371
installment                   15383
grade                             7
sub_grade                        35
emp_title                     28820
emp_length                       11
home_ownership                    5
annual_inc                     5318
verification_status               3
issue_d                          55
loan_status                       3
pymnt_plan                        1
desc                          26527
purpose                          14
title                         19615
zip_code                        823
addr_state                       50
dti                            2868
initial_list_status               1
collections_12_mths_ex_med        1
policy_code                       1
acc_now_delinq                    1
chargeoff_within_12_mths          1
delinq_amnt                       1
pub_rec_bankruptcies              3
tax_liens                         1
dtype: int64

Here, we can directly drop columns that have just one value as these metrics will not affect the analysis

col_to_drop = ['pymnt_plan',
               'initial_list_status',
               'collections_12_mths_ex_med',
               'policy_code',
               'acc_now_delinq',
               'chargeoff_within_12_mths',
               'delinq_amnt',
               'tax_liens'
              ]
loan = loan.drop(col_to_drop, axis = 1)
loan.shape

(39717, 24)

E. Remove free text columns

Free text columns cannot be used for analysis since they would lack a pattern required for analysis. So drop them.

col_to_be_dropped = ['desc','title']
loan = loan.drop(col_to_be_dropped, axis = 1)
loan.shape

(39717, 22)

F. Remove columns with all unique values

There are 39717 rows. If every value in a particular column is unique, it does not add any perspective to our analysis. Therefore, such columns can be dropped.

#Refer to loan.nunique() in section D for identifying those columns
col_to_be_dropped = ['id','member_id']
loan = loan.drop(col_to_be_dropped, axis = 1)
loan.shape

(39717, 20)

3. Data Understanding

With the columns that we have left for analysis, let us understand what each column represents

1. loan_amnt:

• Amount which has been requested by the borrower from Lending Club as per past records
• This is a continuous integer value

1. funded_amnt:

• Funded amount is the amount which was approved by Lending Club with respect to the loan applied
• This is a continuous integer value
• Funded amount should be less than or equal to loan amount

1. funded_amnt_inv

• Amount funded by the investor for the requested loan amount
• It is a continuous float value
• Funded amount invested should be less than or equal to funded amount

1. term

• Duration in which loan will be repayed
• This can also be seen as installments in which loan is repayed
• In the lending club case study, terms takes two values: 36 Months (short term), 60 Months(long term)

1. int_rate

• Interest rate on the loan (having value in percentage)
• Borrowerer will have to pay actual loan + interest on top of the loan in order to pay off complete loan
• Some of the factors which could influence interest rate could be economic strength, inflation, government policy, supply and demand, credit risk, and loan period
• This is a continuous float value

1. installment

• Fixed amount of money that must be repaid with regularly scheduled payments by borrowers
• It is a continuous float value

1. grade

• Lending Club assigned loan grade
• Loan grades are set based on both the borrower's credit profile and the nature of the contract
• 'A' grade loans represent the lowest risk while 'G' grade loans represent high risk
• In the current case study, grade takes values: A,B,C,D,E,F,G

1. sub_grade

• Lending Club assigned loan subgrade
• With in each loan garde also subgarde assigned to get better risk factors attached with the loans
• For example with in grade A we have [A1, A2, A3, A4, A5] to better categorize the loan

1. emp_title

• The job title supplied by the borrower when applying for the loan. It also represent the organization the borrower works for
• Free text field, can take any value in string domain

1. emp_length

• Employment length in years - employment tenure of the borrower
• Possible values are between 0 and 10 where 0 means less than one year and 10 means ten or more years

1. home_ownership

• The home ownership status provided by the borrower during registration
• Our values are: RENT, OWN, MORTGAGE, OTHER

1. annual_inc

• Annual income provided by the borrower during registration
• Can take any value in float domain

1. verification_status

• Indicates that for the loan if borrowers income was verified by lending club
• It can take below three values with corresponding detals:
  • Verified - Income claimed by borrower is verfied by lending club
  • Source Verified - Income Source is verified
  • Not verified - Neither income, nor income source is verified

1. issue_d

• The month and year in which the loan was funded
• The month of issue can be derived for analysis

1. loan_status

• Indicates current payoff status for loan
• Can take below there values
  • Fully Paid - Loan is fully paid by borowwer
  • Current - Loan is being paid off currently
  • Charged off - Borrower could not pay the loan

1. purpose

• Indicates the purpose for which laon was applied
• Some of the categories are debt consolidation, credit card, small business, housing, vacation etc.

1. zip_code

• The first 3 numbers out of the 5 digits of the zip code provided by the borrower in the loan application.

1. addr_state

• The state provided by the borrower in the loan application

1. dti

• A ratio calculated using the borrower’s total monthly debt payments on the total debt obligations, excluding mortgage and the requested LC loan, divided by the borrower’s self-reported monthly income
• Higher dti corresponds to high risk loans.

1. pub_rec_bankruptcies

• Number of public record of previous bankruptcies

A. Detect Missing Values - Propose imputation or cleanup

Let's take a look at the columns that still have null values and check their proportions.

loan.shape

(39717, 20)

# Identify columns with small percentage of missing data to determine imputation methods.
(loan.isnull().sum()/len(loan))*100

loan_amnt               0.000000
funded_amnt             0.000000
funded_amnt_inv         0.000000
term                    0.000000
int_rate                0.000000
installment             0.000000
grade                   0.000000
sub_grade               0.000000
emp_title               6.191303
emp_length              2.706650
home_ownership          0.000000
annual_inc              0.000000
verification_status     0.000000
issue_d                 0.000000
loan_status             0.000000
purpose                 0.000000
zip_code                0.000000
addr_state              0.000000
dti                     0.000000
pub_rec_bankruptcies    1.754916
dtype: float64

We see a very small percentage of missing data for emp_title, emp_length and pub_rec_bankruptcies. Therefore, the imputation method determined for these rows is to drop these rows.

loan = loan.dropna()
loan.shape

(36548, 20)

B. Loan Status: Filter Out Values Needed For Analysis

loan.loan_status.unique()

array(['Charged Off', 'Fully Paid', 'Current'], dtype=object)

Loan Status takes three values:

• Charged off - Borrowers who have defaulted and have not been able to fully pay.
• Fully Paid - Borrowers who have completed their loan payment.
• Current - Borrowers who are currently replaying their loan amounts.

The goal of our analysis is to identify the kind of potential borrowers who will default and therefore, will have to be charged off - Investors must not invest in them. In addition, this will also help understand borrowers who will pay off fully.

Therefore, for our analysis, borrowers who are currently paying off their loans (loan_status = Current) are not required. We will remove them from our dataset.

loan = loan[loan.loan_status != 'Current']
loan.shape

(35482, 20)

Now that we have cleaned up the data, we move on to Exploratory Data Analysis.

C. Data Overview

loan.head()

	loan_amnt	funded_amnt	funded_amnt_inv	term	int_rate	installment	grade	sub_grade	emp_title	emp_length	home_ownership	annual_inc	verification_status	issue_d	loan_status	purpose	zip_code	addr_state	dti	pub_rec_bankruptcies
1	2500	2500	2500.0	60 months	15.27%	59.83	C	C4	Ryder	< 1 year	RENT	30000.0	Source Verified	Dec-11	Charged Off	car	309xx	GA	1.00	0.0
3	10000	10000	10000.0	36 months	13.49%	339.31	C	C1	AIR RESOURCES BOARD	10+ years	RENT	49200.0	Source Verified	Dec-11	Fully Paid	other	917xx	CA	20.00	0.0
5	5000	5000	5000.0	36 months	7.90%	156.46	A	A4	Veolia Transportaton	3 years	RENT	36000.0	Source Verified	Dec-11	Fully Paid	wedding	852xx	AZ	11.20	0.0
6	7000	7000	7000.0	60 months	15.96%	170.08	C	C5	Southern Star Photography	8 years	RENT	47004.0	Not Verified	Dec-11	Fully Paid	debt_consolidation	280xx	NC	23.51	0.0
7	3000	3000	3000.0	36 months	18.64%	109.43	E	E1	MKC Accounting	9 years	RENT	48000.0	Source Verified	Dec-11	Fully Paid	car	900xx	CA	5.35	0.0

# Identifying object types for the data
loan.info()

<class 'pandas.core.frame.DataFrame'>
Int64Index: 35482 entries, 1 to 39680
Data columns (total 20 columns):
 #   Column                Non-Null Count  Dtype  
---  ------                --------------  -----  
 0   loan_amnt             35482 non-null  int64  
 1   funded_amnt           35482 non-null  int64  
 2   funded_amnt_inv       35482 non-null  float64
 3   term                  35482 non-null  object 
 4   int_rate              35482 non-null  object 
 5   installment           35482 non-null  float64
 6   grade                 35482 non-null  object 
 7   sub_grade             35482 non-null  object 
 8   emp_title             35482 non-null  object 
 9   emp_length            35482 non-null  object 
 10  home_ownership        35482 non-null  object 
 11  annual_inc            35482 non-null  float64
 12  verification_status   35482 non-null  object 
 13  issue_d               35482 non-null  object 
 14  loan_status           35482 non-null  object 
 15  purpose               35482 non-null  object 
 16  zip_code              35482 non-null  object 
 17  addr_state            35482 non-null  object 
 18  dti                   35482 non-null  float64
 19  pub_rec_bankruptcies  35482 non-null  float64
dtypes: float64(5), int64(2), object(13)
memory usage: 5.7+ MB

4. Univariate Analysis

A. Loan Status

• Definition: Has the loan been paid fully or has it been charged off?
• Type: Categorical
• Values: Fully Paid, Charged Off (2)

# Calculate the Percentage of Defaulters (Borrowers with Charged Off Loans) at Lending Club

loan_status_per = (loan.loan_status.value_counts(normalize=True))*100
loan_status_per

Fully Paid     85.8548
Charged Off    14.1452
Name: loan_status, dtype: float64

# Plot a Bar Chart to Visualize the Percentage of Defaulters at Lending Club

x_values = loan_status_per.index
y_values = loan_status_per.values


fig = go.Figure(data=go.Bar(x=x_values, y=y_values))

bar_width = 0.4
fig.update_traces(width=bar_width)

fig.update_layout(title='Loan Status Overview in Percentage', 
                  xaxis_title='Loan Status', 
                  yaxis_title='Percentage of Loans',
                  width=500,  
                  height=500)
fig.show()

Insight:

• 14.14% of loans are being charged off at Lending Club.
• Our goal is to ensure this overall percentage is being reduced.
• Therefore, in our EDA, we will focus on metrics that are influencing the percentage of charged off loans.
• That is, technically, every categorical and numeric variable will be viewed as a percentage of charged off loans.

In our analysis, 14.14% is being considered as the threshold amount. Any metric that affects the loan status (charged-off loan percentage) more than this threshold value needs to be considered with caution.

B. Loan Amount

1. loan_amnt:

• Amount which has been requested by the borrower from Lending Club as per past records
• This is a continuous integer value

1. funded_amnt:

• Funded amount is the amount which was approved by Lending Club with respect to the loan applied
• This is a continuous integer value
• Funded amount should be less than or equal to loan amount

1. funded_amnt_inv

• Amount funded by the investor for the requested loan amount
• It is a continuous float value
• Funded amount invested should be less than or equal to funded amount

Basic Data Cleansing

• loan_amnt should be greater than or equal to funded_amnt
• funded_amnt should be greater than or equal to funded_amnt_inv

(loan.loan_amnt >= loan.funded_amnt).all()

True

(loan.funded_amnt >= loan.funded_amnt_inv).all()

True

# Plot a histogram to View the Spread of Loan Amounts

fig = go.Figure(data=go.Histogram(x=loan.loan_amnt))

fig.update_layout(title='Distribution of Loan Amount', 
                  xaxis_title='Loan Amount', 
                  yaxis_title='Frequency',
                  height = 700,
                  xaxis=dict(showgrid=True),
                  yaxis=dict(showgrid=True))

fig.show()

Insight:

• Maximum loans have been given between 5k-15k.

# Plot a box plot to view the spread of loan amounts Over Loan Status

fig = go.Figure(data=go.Box(x=loan.loan_status, y=loan.loan_amnt))

fig.update_layout(title='Spread of Loan Amount Across Various Loan Status', 
                  yaxis_title='Loan Amount',
                  xaxis_title='Loan Status',
                  height = 700,
                  width = 700
                  )

# Display the chart
fig.show()

Insight:

• The plot depicting the Charged Off loans is slightly right skewed.
• In comparison to the loans being fully paid, higher amount loans appear to be charged off more.

# Loan Amount is a Numerical Variable. Creating Bins Helps With Better Analysis.

# Define the loan amount bins similar to the bins taken by the box plot graph
bins = [0, 5000, 10000, 15000, 20000, 25000, 30000, 35000]

# Create a new column 'loan_amnt_bin' to categorize loan amounts into bins
loan['loan_amnt_bin'] = pd.cut(loan['loan_amnt'], bins)

#Plotly cannot handle category datatype, so convert to str in a new column for plotting graphs
loan['loan_amnt_bin_str'] = loan['loan_amnt_bin'].astype(str)

#Category array will be used as a variable in plotly graphs since string variables tend to get sorted while plotting.
#Here, we are preserving the order
loan_amnt_cat_arr = ['(0, 5000]','(5000, 10000]','(10000, 15000]','(15000, 20000]',
                     '(20000, 25000]','(25000, 30000]','(30000, 35000]']

#Charged-off loans dataset
loan_npaid = loan[loan.loan_status =='Charged Off']
#Fully-paid loans dataset
loan_fpaid = loan[loan.loan_status =='Fully Paid']

# Plot a Graph: Percentage of Loans Charged Off or Fully Paid per Loan Amount Bin 

loan_count = loan.groupby(['loan_amnt_bin_str', 'loan_status']).size().unstack()
loan_count = loan_count.reset_index()
loan_count['npaid_ratio'] = round((loan_count['Charged Off'] / 
                                   (loan_count['Charged Off'] + 
                                    loan_count['Fully Paid']))*100,2)
loan_count['fpaid_ratio'] = round((loan_count['Fully Paid'] / 
                                   (loan_count['Charged Off'] + 
                                    loan_count['Fully Paid']))*100,2)

fig = go.Figure()

# Add Bar trace for Charged Off Loans

fig.add_trace(go.Bar(
    x=loan_count.loan_amnt_bin_str,
    y=loan_count.npaid_ratio,
    name='Charged-Off Loans',
    offset=-0.2,
    marker=dict(color='Red')
))

# Add Bar trace for Fully Paid Loans
fig.add_trace(go.Bar(
    x=loan_count.loan_amnt_bin_str,
    y=loan_count.fpaid_ratio,
    name='Fully Paid Loans',
    offset=0.2,
    marker=dict(color='Green')
))

bar_width = 0.4
fig.update_traces(width=bar_width)

# Update the layout
fig.update_layout(
    title='Loan Status Across Various Loan Amount Bins',
    xaxis_title='Loan Amount Bins',
    yaxis_title='Percentage of Loans',
    xaxis=dict(categoryorder='array', categoryarray=loan_amnt_cat_arr),
    barmode='group'
)

# Display the chart
fig.show()

Insights:

• Loans of high value (30k-35k) are being charged off the most(24.07%) which means high value loans are high risk.
• Loans greater than 15k are being charged off more than 15% which is higher than the overall 14.14% threshold value we have taken.
• Loans between 5000-10000 are being charged off the least(12.42%) and paid off the most.

Funded Amount Invested - Confidence of Investors

If funded_amnt_inv is equal to loan_amnt, it implies that the investor had full confidence on the borrower. Let us analyze if the confidence of the investor has any impact on the percentage of charged off loans.

# Create a New Column to State if the Lender Had Full Confidence in the Borrower or Not

loan['conf'] = np.where(loan['loan_amnt'] == loan['funded_amnt_inv'], 'Yes', 'No')
#Charged-off loans dataset
loan_npaid = loan[loan.loan_status =='Charged Off']
#Fully-paid loans dataset
loan_fpaid = loan[loan.loan_status =='Fully Paid']

loan.conf.value_counts()

No     17816
Yes    17666
Name: conf, dtype: int64

# Plot a Graph to Show: Did the Confidence of the Investor Have Any Impact on the Repayment of Loans?

fig = go.Figure(data=go.Bar(x=loan.conf.value_counts().index, 
                            y=loan.conf.value_counts().values))

bar_width = 0.4
fig.update_traces(width=bar_width)

fig.update_layout(title='Number of Loans Over Confidence of Investors', 
                  xaxis_title='Did the Investor Have Full Confidence?', 
                  yaxis_title='Number of Loans',
                  width=500,  
                  height=500)
fig.show()

Insight:

• The investors have had an equally mixed opinion on the borrowers.
• Overall, the investors have had full confidence while issuing 50% of the loans which turns out to be ~17.6k loans.

# Filter Out Loans Where Investor Had Full Confidence

conf_npaid = loan_npaid.conf.value_counts().sort_index()
conf_total = loan.conf.value_counts().sort_index()
conf_per = ( conf_npaid / conf_total )*100
conf_per

No     14.475752
Yes    13.811842
Name: conf, dtype: float64

# Plot a Graph to Show: Did the Confidence of the Investor Have Any Impact on the Repayment of Loans?

x_values = conf_per.index
y_values = conf_per.values

fig = go.Figure(data=go.Bar(x=x_values, y=y_values))

bar_width = 0.4
fig.update_traces(width=bar_width)

fig.update_layout(title='Percentage of Charged-Off Loans Over Confidence of Investors', 
                  xaxis_title='Did the Investor Have Full Confidence?', 
                  yaxis_title='Percentage of Defaulters',
                  width=500,  
                  height=500)
fig.show()

Insight:

• Of the given out ~17.6k loans where the investors have had full confidence, 13.81% have been charged-off.
• Likewise, of the given out ~17.8k loans where the investors have not had full confidence, 14.4% have been charged-off.
• The confidence of the investors does not have any direct impact on the repayment of loans.
• We will exclude this variable for further analysis.

C. Loan Term

• Duration in which loan will be repayed
• This can also be seen as installments in which loan is repayed
• In the lending club case study, terms takes two values: 36 Months (short term), 60 Months(long term)

# Plot a graph to display number of loans given in each loan term

term_total = loan.term.value_counts().sort_index()

fig = go.Figure()
fig.add_trace(go.Bar(
    x=term_total.index,
    y=term_total.values,
    name='Loan Term',
    marker=dict(color='Blue')
))
bar_width = 0.2
fig.update_traces(width=bar_width)
fig.update_layout(
    title='Number of Loans Over Loan Terms',
    xaxis_title='Loan Term',
    yaxis_title='Number of Loans'
)
fig.show()

Insight:

• Borrowers tend to go for shorter loan term of 36 months.
• 26.47k people have opted for a short term loan which is about 75% of the total loans given.

# Plot a Graph to Display Percentage of Charged Off Loans Across Both the Loan Terms

term_npaid = loan_npaid.term.value_counts().sort_index()
term_fpaid = loan_fpaid.term.value_counts().sort_index()
term_npaid_per = (term_npaid.values / term_total.values)*100

fig = go.Figure()
fig.add_trace(go.Bar(
    x=term_total.index,
    y=term_npaid_per,
    name='Loan Term',
    marker=dict(color='Blue')
))
bar_width = 0.2
fig.update_traces(width=bar_width)
fig.update_layout(
    title='Variation in Debtors Over Loan Terms',
    xaxis_title='Loan Term',
    yaxis_title='Percentage of Charged Off Loans'
)
fig.show()

Insight:

• Only 10% of loans with a 36 month term are being charged off, i.e., only 10% of 26.47k loans are being charged off.
• 24.76% of loans with a 60 month term are being charged off. This is way higher than the threshold value.
• Further insights on this metric can be derived during bivariate analysis with other metrics.

D. Interest Rate

• Interest rate on the loan (having value in percentage)
• Borrowerer will have to pay actual loan + interest on top of the loan in order to pay off complete loan
• Some of the factors which could influence interest rate could be economic strength, inflation, government policy, supply and demand, credit risk, and loan period
• This is a continuous float value

# Convert loan rate to float object
loan.int_rate = loan.int_rate.apply(lambda x: float( x[:-1]))

# Plot a Histogram to View the Spread of Interest rates
fig = go.Figure(data=go.Histogram(x=loan.int_rate, nbinsx=20))

fig.update_layout(title='Distribution of Interest Rate', 
                  xaxis_title='Interest Rate', 
                  yaxis_title='Frequency',
                  height = 500,
                  xaxis=dict(showgrid=True),
                  yaxis=dict(showgrid=True))

fig.show()

Insight:

• Most loans have an interest rate between 7 to 13%.

# Plot a Graph to Dispaly Spread of Interest Rate Over Loan Status

fig = go.Figure(data=go.Box(x=loan.loan_status, y=loan.int_rate))
fig.update_layout(title='Spread of Interest Rate Over Loan Status', 
                  yaxis_title='Loan Interest Rate',
                  xaxis_title='Loan Status',
                  height = 700,
                  width = 700
                  )
fig.show()

Insight:

• The plot displaying charged off loans is right skewed.
• Higher interest rate loans are being charged off more with a median interest rate of 13.8%.

# Plot a Graph to Display Percentage of Charged Off Loans Across Interest Rates.
# Since Interest Rate is a Numeric Variable, we will bucket it first.

loan['int_rate_bin'] = round(loan.int_rate)

#Charged-off loans dataset
loan_npaid = loan[loan.loan_status =='Charged Off']
#Fully-paid loans dataset
loan_fpaid = loan[loan.loan_status =='Fully Paid']

int_rate_npaid = loan_npaid.int_rate_bin.value_counts().sort_index()
int_rate_total = loan.int_rate_bin.value_counts().sort_index()
int_rate_npaid_per = (int_rate_npaid.values / int_rate_total.values)*100

fig = go.Figure()
fig.add_trace(go.Bar(
    x=int_rate_total.index,
    y=int_rate_npaid_per,
    name='Loan Term',
    marker=dict(color='Blue')
))
bar_width = 0.2
fig.update_traces(width=bar_width)
fig.update_layout(
    title='Variation in Debtors Over Loan Terms',
    xaxis_title='Interest Rate',
    yaxis_title='Percentage of Charged Off Loans',
)
fig.show()

Insight:

• It is very evident that as interest rate goes up, percentage of charged off loans also increases.
• Loans with interest rate greater than or equal to 12% are being charged off more than 14.5% which is above the threshold value determined for our analysis.
• More than 30% of the loans with interest rate greater than 19% are being charged off which is twice the threshold value.

E. Installment

• Fixed amount of money that must be repaid with regularly scheduled payments by borrowers
• It is a continuous float value

# Plot a Histogram to View the Spread of Monthly Installments

fig = go.Figure(data=go.Histogram(x=loan.installment, nbinsx=20))

fig.update_layout(title='Distribution of Monthly Installments', 
                  xaxis_title='Monthly Installments', 
                  yaxis_title='Frequency',
                  height = 700,
                  xaxis=dict(showgrid=True),
                  yaxis=dict(showgrid=True))

fig.show()

Insight:

• Most loans have a monthly installment between 100 to 400.

# Plot a Graph to Dispaly Spread of Monthly Installment Amount Over Loan Status

fig = go.Figure(data=go.Box(x=loan.loan_status, y=loan.installment))
fig.update_layout(title='Spread of Monthly Installment Amount Over Loan Status', 
                  yaxis_title='Monthly Installment Amount',
                  xaxis_title='Loan Status',
                  height = 700,
                  width = 700
                  )
fig.show()

Insight:

• The plot displaying charged off loans is slightly right skewed.
• Number of loans with higher monthly installments being charged off is more with a median installment amount of 296.84.

# Plot a Graph to Display Percentage of Charged Off Loans Across Monthly Installment Amounts.
# Since Monthly Installment is a Numeric Variable, we will bucket it first.

# Define the installment bins similar to the bins taken by the box plot graph
bins = [0, 200, 400, 600, 800, 1000, 1200, 1400]

# Create a new column 'loan_amnt_bin' to categorize loan amounts into bins
loan['installment_bin'] = pd.cut(loan['installment'], bins)

#Plotly cannot handle category datatype, so convert to str in a new column for plotting graphs
loan['installment_bin_str'] = loan['installment_bin'].astype(str)

#Category array will be used as a variable in plotly graphs since string variables tend to get sorted while plotting.
#Here, we are preserving the order
installment_cat_arr = ['(0, 200]','(200, 400]','(400, 600]','(600, 800]',
                     '(800, 1000]','(1000, 1200]','(1200, 1400]']

#Charged-off loans dataset
loan_npaid = loan[loan.loan_status =='Charged Off']
#Fully-paid loans dataset
loan_fpaid = loan[loan.loan_status =='Fully Paid']

# Plot a Graph: Percentage of Loans Charged Off or Fully Paid per Installment Amount Bin 

loan_count = loan.groupby(['installment_bin_str', 'loan_status']).size().unstack()
loan_count = loan_count.reset_index()
loan_count['npaid_ratio'] = round((loan_count['Charged Off'] / 
                                   (loan_count['Charged Off'] + 
                                    loan_count['Fully Paid']))*100,2)
loan_count['fpaid_ratio'] = round((loan_count['Fully Paid'] / 
                                   (loan_count['Charged Off'] + 
                                    loan_count['Fully Paid']))*100,2)

fig = go.Figure()
fig.add_trace(go.Bar(
    x=loan_count.installment_bin_str,
    y=loan_count.npaid_ratio,
    name='Charged-Off Loans',
    offset=-0.2,
    marker=dict(color='Red')
))
fig.add_trace(go.Bar(
    x=loan_count.installment_bin_str,
    y=loan_count.fpaid_ratio,
    name='Fully Paid Loans',
    offset=0.2,
    marker=dict(color='Green')
))
bar_width = 0.4
fig.update_traces(width=bar_width)
fig.update_layout(
    title='Loan Status Across Various Monthly Installment Amount Bins',
    xaxis_title='Installment Amount Bins',
    yaxis_title='Percentage of Loans',
    xaxis=dict(categoryorder='array', categoryarray=installment_cat_arr),
    barmode='group'
)
fig.show()

Insight:

• Loans with installment amount between 400 and 1000 are being charged off more than 14.5% which is above the threshold value determined for our analysis.
• It is interesting to note that for extremely high installment amount, the charge off % is lesser compared to the mid-range. It is possible that people with higher monthly installment are likeyly to close their loans since they will incur higher penalty when not done in time.

F. Grade

• Lending Club assigned loan grade
• Loan grades are set based on both the borrower's credit profile and the nature of the contract
• 'A' grade loans represent the lowest risk while 'G' grade loans represent high risk
• In the current case study, grade takes values: A,B,C,D,E,F,G

# Plot a graph to display number of loans given for each grade

grade_total = loan.grade.value_counts().sort_index()

fig = go.Figure()
fig.add_trace(go.Bar(
    x=grade_total.index,
    y=grade_total.values,
    name='Grade of Loan',
    marker=dict(color='Blue')
))
bar_width = 0.2
fig.update_traces(width=bar_width)
fig.update_layout(
    title='Number of Loans Over Loan Grade',
    xaxis_title='Loan Grade',
    yaxis_title='Number of Loans'
)
fig.show()

Insight:

• More than 30% borrowers (~10.73k loans) tend to apply for B grade loans.
• A, B and C grade loans contirbute to over 75% of the loans issued.

# Plot a Graph to Display Percentage of Charged Off Loans Across Loan Grades

grade_npaid = loan_npaid.grade.value_counts().sort_index()
grade_fpaid = loan_fpaid.grade.value_counts().sort_index()
grade_npaid_per = (grade_npaid.values / grade_total.values)*100

fig = go.Figure()
fig.add_trace(go.Bar(
    x=grade_total.index,
    y=grade_npaid_per,
    name='Loan Grade',
    marker=dict(color='Blue')
))
bar_width = 0.2
fig.update_traces(width=bar_width)
fig.update_layout(
    title='Variation in Debtors Over Loan Grades',
    xaxis_title='Loan Grade',
    yaxis_title='Percentage of Charged Off Loans'
)
fig.show()

Insight:

• It is evident that high risk loans (loans of high grade) are being charged off more although they are given out less in number ( as per previous analysis).
• It can be further analyzed in the bivariate if grade is correlated to any other variable such as interest rate to see if high risk loans are categorized by high interest rates too.
• High-risk loans with grades (E, F, G) are being charged off at ~30% which is twice the threshold value for our analysis.
• Loans of grade C, D, E, F, G have a charge off rate greater than 15% - this exceed the set threshold value for our analysis.

G. Sub Grade

• Lending Club assigned loan subgrade
• With in each loan garde also subgarde assigned to get better risk factors attached with the loans
• For example with in grade A we have [A1, A2, A3, A4, A5] to better categorize the loan

# Plot a graph to display number of loans given for each sub-grade

sub_grade_total = loan.sub_grade.value_counts().sort_index()

fig = go.Figure()
fig.add_trace(go.Bar(
    x=sub_grade_total.index,
    y=sub_grade_total.values,
    name='Sub Grade of Loan',
    marker=dict(color='Blue')
))
bar_width = 0.2
fig.update_traces(width=bar_width)
fig.update_layout(
    title='Number of Loans Over Loan Sub Grade',
    xaxis_title='Loan Sub Grade',
    yaxis_title='Number of Loans'
)
fig.show()

Insight:

• The sub grade graph gives detailed insights into the distribution of loans when compared to the grade graph.
• Although, the insights are similar, this graph helps us indentify which sub grades have most loans given out.
• In particular, it can be seen that ~40% of the loans are given out between A4 and C2.

# Plot a Graph to Display Percentage of Charged Off Loans Across Loan Sub Grades

sub_grade_npaid = loan_npaid.sub_grade.value_counts().sort_index()
sub_grade_fpaid = loan_fpaid.sub_grade.value_counts().sort_index()
sub_grade_npaid_per = (sub_grade_npaid.values / sub_grade_total.values)*100

fig = go.Figure()
fig.add_trace(go.Bar(
    x=sub_grade_total.index,
    y=sub_grade_npaid_per,
    name='Loan Sub Grade',
    marker=dict(color='Blue')
))
bar_width = 0.2
fig.update_traces(width=bar_width)
fig.update_layout(
    title='Variation in Debtors Over Loan Sub Grades',
    xaxis_title='Loan Sub Grade',
    yaxis_title='Percentage of Charged Off Loans'
)
fig.show()

Insights:

• Higher grade loans are high risk loans - they are being charged off more.
• Loans above C grade are being charged off more than 15% which is more than the threshold value.
• Loans in E4 and above have a charge off percentage greater than 28 which is twice more than the set threshod for analysis.
• Since sub-grades give deeper insights, the grade metric will be ignored for future analysis.

H. Employee Title

• The job title supplied by the borrower when applying for the loan. It also represent the organization the borrower works for
• Free text field, can take any value in string domain

#Calculate Number of Borrowers Across Various Employee Titles
emp_title_total = ((loan.emp_title.value_counts(normalize=True))*100)
emp_title_total

US Army                                 0.363565
Bank of America                         0.298743
IBM                                     0.183191
AT&T                                    0.160645
Kaiser Permanente                       0.155008
                                          ...   
Togo's                                  0.002818
Fox Restaurant Concepts                 0.002818
ieb local1                              0.002818
city of new haven board of education    0.002818
Fairfield Manufacturing                 0.002818
Name: emp_title, Length: 27506, dtype: float64

Insight:

There are 27506 unique employee titles. Categorizing these values might only result in a very small percentage of borrower who can be grouped together.

Let us confirm with a visualization

# Plot a Graph to Display Number of Loans Per Employee Title
fig = go.Figure()
fig.add_trace(go.Bar(
    x=emp_title_total.head(15).index,
    y=emp_title_total.head(15).values,
    name='Employee Title',
    marker=dict(color='Blue')
))
bar_width = 0.2
fig.update_traces(width=bar_width)
fig.update_layout(
    title='Overview of Borrowers Across Employee Titles',
    xaxis_title='Employee Title',
    yaxis_title='Percentage of Borrowers',

)
fig.show()

Insight:

• <1% of borrowers have the same employee title. Grouping borrowers under this metric will not give us any effective analysis.
• It is a free text field. Example, US Army and U.S. Army are two values observed. Although they might mean the same, it is difficult to comprehend how many such different values are to be grouped together.
• Another example of similar kind is that of USAF, US Air Force, United States Air Force. It is unclear if USAF is different or same as US Air Force. Therefore, imputation also cannot be performed.
• Therefore, the employee title metric will be ignored for future analysis.

I. Employee Length

• Employment length in years - employment tenure of the borrower
• Possible values are between 0 and 10 where 0 means less than one year and 10 means ten or more years

# Plot a Graph to Display Number of Loans Across Employee Tenure
emp_length_total = loan.emp_length.value_counts().sort_index()

fig = go.Figure()
fig.add_trace(go.Bar(
    x=emp_length_total.index,
    y=emp_length_total.values,
    name='Emploee Tenure',
    marker=dict(color='Blue')
))
bar_width = 0.2
fig.update_traces(width=bar_width)
emp_len_cat_arr = ['< 1 year','1 year','2 years','3 years','4 years','5 years',
                   '6 years','7 years','8 years','9 years','10+ years']
fig.update_layout(
    title='Number of Loans Over Employee Tenure',
    xaxis_title='Employee Tenure',
    yaxis_title='Number of Loans',
    xaxis=dict(categoryorder='array', categoryarray=emp_len_cat_arr)
)
fig.show()

Insights:

• People with the highest employment tenure tend to apply for more loans. More than 8000 loans which contirbute to ~22% of the loans taken are by people with highest employment tenure (>10 years).
• Further analysis on why people take loans after 10+ years can be analysed.
• It can be observed that less experienced people and people with more than 10+ year experience are taking more loans.

# Plot a Graph to Display Percentage of Charged Off Loans Across Employee Tenure

emp_length_npaid = loan_npaid.emp_length.value_counts().sort_index()
emp_length_fpaid = loan_fpaid.emp_length.value_counts().sort_index()
emp_length_npaid_per = (emp_length_npaid.values / emp_length_total.values)*100

fig = go.Figure()
fig.add_trace(go.Bar(
    x=emp_length_total.index,
    y=emp_length_npaid_per,
    name='Employee tenure',
    marker=dict(color='Blue')
))
bar_width = 0.2
fig.update_traces(width=bar_width)
fig.update_layout(
    title='Variation in Debtors Over Employee Tenures',
    xaxis_title='Employee Tenure',
    yaxis_title='Percentage of Charged Off Loans',
    xaxis=dict(categoryorder='array', categoryarray=emp_len_cat_arr)
)
fig.show()

Insights:

• There is no significant difference in the percentage of debtors across employment tenures
• In all the tenures, loans are being charged off at a rate closer to the threshold value taken for analysis (~14%)
• Employee length metric does not have any significant impact on loans being charged off

J. Home Ownership

• The home ownership status provided by the borrower during registration
• Our values are: RENT, OWN, MORTGAGE, OTHER

# Plot Graph to Display Number of Loans Across Home Ownership Categories

home_ownership_total = loan.home_ownership.value_counts().sort_index()

fig = go.Figure()
fig.add_trace(go.Bar(
    x=home_ownership_total.index,
    y=home_ownership_total.values,
    name='Home Ownership Status',
    marker=dict(color='Blue')
))
bar_width = 0.2
fig.update_traces(width=bar_width)
fig.update_layout(
    title='Number of Borrowers In Various Home Ownership Status',
    xaxis_title='Home Ownership Status',
    yaxis_title='Number of Borrowers',
)
fig.show()

Insight:

• People on Rent and Mortgage contribute to over 90% of total loans taken (~32k loans).
• People under 'Other Category' are very less (less than 1%) and can be ignored for future analysis.

# Plot Graph to Display Percentage of Charged Off Loans Across Home Ownership Categories
home_ownership_npaid = loan_npaid.home_ownership.value_counts().sort_index()
home_ownership_npaid_per = (home_ownership_npaid.values / home_ownership_total.values)*100

fig = go.Figure()
fig.add_trace(go.Bar(
    x=home_ownership_total.index,
    y=home_ownership_npaid_per,
    name='Home Ownership Status',
    marker=dict(color='Blue')
))
bar_width = 0.2
fig.update_traces(width=bar_width)
fig.update_layout(
    title='Variation in Debtors Over Home Ownership Status (Ignore Other Category)',
    xaxis_title='Home Ownership Status',
    yaxis_title='Percentage of Charged Off Loans',
)
fig.show()

Insight:

• Although we noticed in the previous graph that home ownership category influenced whether a loan was taken or not, once a loan was taken, the rate of charge off doesn't vary much (among mortgage, own, rent) as can be seen in this graph.
• As discussed, we have ignored 'other' category for analysis.

K. Annual Income

• Annual income provided by the borrower during registration
• Can take any value in float domain

# Plot Graph to Display Spread of Annual Income Over Loan Status, understand outliers
fig = go.Figure(data=go.Box(x=loan.loan_status, y=loan.annual_inc))
fig.update_layout(title='Spread of Annual Income Across Various Loan Status', 
                  yaxis_title='Annual Income',
                  xaxis_title='Loan Status',
                  height = 1000,
                  width = 700
                  )
fig.show()

Insight:

• From the box plot and describe, we see that the annual income is widely spread. Annual income varies from 4K to 6M. We will reduce the data to a range that is helpful for analysis.
• We will determine the percentile upto which data will be considered next.

#Check percentile for annual income 

print("85th percentile:",np.percentile(loan.annual_inc,85))
print("90th percentile:",np.percentile(loan.annual_inc,90))
print("95th percentile:",np.percentile(loan.annual_inc,95))

85th percentile: 100000.0
90th percentile: 115000.0
95th percentile: 140000.0

Insight:

We see that 95% of people have a salary of 140k or below. We will consider income from minimum to 140k for our analysis.

# Applying filter in annual income
# Plot a Histogram to Display Spread of Annual Income Over Loan Status

fig = go.Figure(data=go.Histogram(x=loan.annual_inc[loan.annual_inc < 140000]))
fig.update_layout(title='Distribution of Annual Income', 
                  xaxis_title='Annual Income', 
                  yaxis_title='Frequency',
                  height = 700,
                  xaxis=dict(showgrid=True),
                  yaxis=dict(showgrid=True))

fig.show()

Insight:

• People with income between 40k to 60k seem to be taking the most loans.
• As salary increases, people tend to take lesser loans.
• Likewise, even people with lower salaries are taking lesser loans.
• BIn the annual income for further analysis

# Define the annual income bins similar to the bins taken by the box plot graph (majority is below 50000)
bins = [0, 10000, 20000, 30000, 40000, 50000, 60000, 70000, 80000, 90000, 100000, 
        110000, 120000, 130000, 140000]

# Create a new column 'annual_inc_bin' to categorize annual income into bins
loan['annual_inc_bin'] = pd.cut(loan['annual_inc'], bins)

#Plotly cannot handle category datatype, so convert to str in a new column for plotting graphs
loan['annual_inc_bin_str'] = loan['annual_inc_bin'].astype(str)

#Category array will be used as a variable in plotly graphs since string variables tend to get sorted while plotting.
#Here, we are preserving the order
annual_inc_cat_arr = ['(0, 10000]','(10000, 20000]','(20000, 30000]','(30000, 40000]',
                      '(40000, 50000]','(50000, 60000]','(60000, 70000]','(70000, 80000]',
                      '(80000, 90000]','(90000, 100000]','(100000, 110000]','(110000, 120000]',
                      '(120000, 130000]','(130000, 140000]']

#Redefine charged off and fully paid dataset since new column is added
#Charged-off loans dataset
loan_npaid = loan[loan.loan_status =='Charged Off']
#Fully-paid loans dataset
loan_fpaid = loan[loan.loan_status =='Fully Paid']

#Charged-off loans dataset
loan_annual_inc = loan[loan.annual_inc <= 140000]
loan_annual_inc_npaid = loan_annual_inc[loan_annual_inc.loan_status =='Charged Off']

# Count how many loans were charged off or fully paid in each annual income bin
loan_annual_inc = loan[loan.annual_inc <= 140000]
loan_count = loan_annual_inc.groupby(['annual_inc_bin_str', 'loan_status']).size().unstack()
loan_count = loan_count.reset_index()
loan_count['npaid_ratio'] = round((loan_count['Charged Off'] / (loan_count['Charged Off'] + loan_count['Fully Paid']))*100,2)
loan_count['fpaid_ratio'] = round((loan_count['Fully Paid'] / (loan_count['Charged Off'] + loan_count['Fully Paid']))*100,2)

fig = go.Figure()
fig.add_trace(go.Bar(
    x=loan_count.annual_inc_bin_str,
    y=loan_count.npaid_ratio,
    name='Charged-Off Loans',
    offset=-0.2,
    marker=dict(color='Red')
))
fig.add_trace(go.Bar(
    x=loan_count.annual_inc_bin_str,
    y=loan_count.fpaid_ratio,
    name='Fully Paid Loans',
    offset=0.2,
    marker=dict(color='Green')
))
bar_width = 0.4
fig.update_traces(width=bar_width)
fig.update_layout(
    title='Loan Status Across Various annual income Bins',
    xaxis_title='Annual income Bins',
    yaxis_title='Percentage of Annual income',
    xaxis=dict(categoryorder='array', categoryarray=annual_inc_cat_arr),
    barmode='group',
    width = 900,
    height = 700
)
fig.show()

Insight:

• As annual income increases, percentage of borrowers defaulting is reducing.
• People with annual income > 60k default less than 15%.

L. Verification Status

• Indicates that for the loan if borrowers income was verified by lending club
• It can take below three values with corresponding detals:
  • Verified - Income claimed by borrower is verfied by lending club
  • Source Verified - Income Source is verified
  • Not verified - Neither income, nor income source is verified

# Plot a Graph to Display the Number of Loans Across Verification Status
verification_status_total = loan.verification_status.value_counts().sort_index()

fig = go.Figure()
fig.add_trace(go.Bar(
    x=verification_status_total.index,
    y=verification_status_total.values,
    name='Verification Status',
    marker=dict(color='Blue')
))
bar_width = 0.2
fig.update_traces(width=bar_width)
fig.update_layout(
    title='Number of Loans Over Verification Status',
    xaxis_title='Verification Status',
    yaxis_title='Number of Loans'
)
fig.show()

Insight:

• About 40% of the loans are given without any verification while for the remaining 60%, either the income or the source of income is being verified.

# Plot a Graph to Display Percentage of Charged Off Loans Over Verification Status
verification_status_npaid = loan_npaid.verification_status.value_counts().sort_index()
verification_status_fpaid = loan_fpaid.verification_status.value_counts().sort_index()

verification_status_npaid_per = (verification_status_npaid.values / verification_status_total.values)*100

fig = go.Figure()
fig.add_trace(go.Bar(
    x=verification_status_total.index,
    y=verification_status_npaid_per,
    name='Verification Status',
    marker=dict(color='Blue')
))
bar_width = 0.2
fig.update_traces(width=bar_width)
fig.update_layout(
    title='Percentage of Charged Off Loans By Verification Status',
    xaxis_title='Verification Status',
    yaxis_title='Percentage of Charged Off Loans'
)
fig.show()

#Line Chart to be added

Insight:

• The verification status does not have any considerable impact on loans being charged off.
• In fact, the verified loans seem to be charged off at an average of 15% which is closer to the threshold value.
• We can derive that the verification status of income of a borrower does not impact the payment of loans.

M. Issue Date

• The month and year in which the loan was funded
• The month of issue can be derived for analysis

# Derive month column from issue_d for further analysis
loan.issue_d.value_counts().head()

Dec-11    1918
Nov-11    1909
Oct-11    1795
Sep-11    1786
Aug-11    1693
Name: issue_d, dtype: int64

loan['issue_month'] = loan.issue_d.apply(lambda x: x[:3])
#Charged-off loans dataset
loan_npaid = loan[loan.loan_status =='Charged Off']
#Fully-paid loans dataset
loan_fpaid = loan[loan.loan_status =='Fully Paid']

Insight:

• When we do a value count, we see that we have data spread over a period of 5 years (2007 to 2011).
• We derive month of issue in order to analyze if a particular month has had any impact on the payment of loans over the last five years.
• There is no need to derive the year separately for analysis since all years are in the past and cannot be used to predict the future

Month of Issue of Loan

# Plot a Graph to Display the Number of Loans Taken Across Various Months
issue_month_total = loan.issue_month.value_counts().sort_index()
issue_month_cat_arr = ['Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec']

fig = go.Figure()
fig.add_trace(go.Bar(
    x=issue_month_total.index,
    y=issue_month_total.values,
    name='Month of Issue',
    marker=dict(color='Blue')
))
bar_width = 0.2
fig.update_traces(width=bar_width)
fig.update_layout(
    title='Number of Loans Taken Over Various Months',
    xaxis_title='Month of Issue',
    yaxis_title='Number of Loans',
    xaxis=dict(categoryorder='array', categoryarray=issue_month_cat_arr),
)
fig.show()

Insight:

• Maximum loans are taken in the months of November and December. One can guess that it is due to the holiday season (Black Friday Sale, Thanksgiving, Christmas) where expenditure is high.
• The number of loans taken drops to almost half in January. One can guess it is due to the holiday season that has come to an end already.

# Plot a Graph to Display Percentage of Charged Off Loans Over Month of Issue
issue_month_npaid = loan_npaid.issue_month.value_counts().sort_index()

issue_month_npaid_per = (issue_month_npaid.values / issue_month_total.values)*100

fig = go.Figure()
fig.add_trace(go.Bar(
    x=issue_month_total.index,
    y=issue_month_npaid_per,
    name='Month of Issue',
    marker=dict(color='Blue')
))
bar_width = 0.2
fig.update_traces(width=bar_width)
fig.update_layout(
    title='Percentage of Charged Off Loans By Month of Issue',
    xaxis_title='Month of Issue',
    yaxis_title='Percentage of Charged Off Loans',
    xaxis=dict(categoryorder='array', categoryarray=issue_month_cat_arr),
)
fig.show()

Insight:

• The month of issue of loan is not paying a significant role on the repayment of loans.

N. Loan Purpose

• Indicates the purpose for which laon was applied
• Some of the categories are debt consolidation, credit card, small business, housing, vacation etc.

# Plot a Graph to Display the Number of Loans Taken For Various Purposes
purpose_total = (loan.purpose.value_counts()).sort_index()

fig = go.Figure()
fig.add_trace(go.Bar(
    x=purpose_total.index,
    y=purpose_total.values,
    name='Loan Purpose',
    marker=dict(color='Blue')
))
bar_width = 0.2
fig.update_traces(width=bar_width)
fig.update_layout(
    title='Number of Loans Taken For Various Purposes',
    xaxis_title='Loan Purpose',
    yaxis_title='Number of Loans'
)
fig.show()

Insight:

• Nearly 50% of the loans are given for debt consolidation followed by credit card purposes.

purpose_npaid = loan_npaid.purpose.value_counts().sort_index()
purpose_npaid_per = (purpose_npaid.values / purpose_total.values)*100

fig = go.Figure()
fig.add_trace(go.Bar(
    x=purpose_total.index,
    y=purpose_npaid_per,
    name='Charged Off Loans',
    marker=dict(color='Blue')
))
bar_width = 0.2
fig.update_traces(width=bar_width)
fig.update_layout(
    title='Variation in Charged of Loans Across Various Loan Purposes',
    xaxis_title='Purpose of Loan',
    yaxis_title='Percentage of Charged Off Loans'
)
fig.show()

Insight:

• 26.7% of all small business borrowers are being charged off, followed closely by renewable energy and educational loans.
• Major purchase, wedding and credit card loans are being paid off the most.

O. Zip Code

• The first 3 numbers out of the 5 digits of the zip code provided by the borrower in the loan application.

# Zip Code has 805 unique values
loan.zip_code.nunique()

805

loan.zip_code.head()

1    309xx
3    917xx
5    852xx
6    280xx
7    900xx
Name: zip_code, dtype: object

Insight:

• It can be seen that the entire 5-digits of the zip code is not visible.
• The first three digits can correspond to the US state. For example, 309xx belongs to Augusta in Georgia.
• Since we have the state information in addr_state column, we will ignore the zip_code metric for analysis.

P. State

• The state provided by the borrower in the loan application

loan.addr_state.nunique()

49

# Plot a Graph to Display the Number of Loans Taken in Various States
addr_state_total = (loan.addr_state.value_counts()).sort_index()

fig = go.Figure()
fig.add_trace(go.Bar(
    x=addr_state_total.index,
    y=addr_state_total.values,
    name='State',
    marker=dict(color='Blue')
))
bar_width = 0.2
fig.update_traces(width=bar_width)
fig.update_layout(
    title='Number of Loans Taken in Various States',
    xaxis_title='State',
    yaxis_title='Number of Loans'
)
fig.show()

Insight:

• The state of CA has the highest number of loans of 6419 which is close to 20% of all loans.
• The next state with high number of loans is NY.
• Most states have less than 1000 loans.

addr_state_npaid = loan_npaid.addr_state.value_counts().sort_index()
print("Total Number of States:",addr_state_total.nunique())
print("Total Number of States Where Loans Were Charged Off:",addr_state_npaid.nunique())

Total Number of States: 47
Total Number of States Where Loans Were Charged Off: 43

Insight:

We can see that there are 4 states that do not have charged off loans. We will remove those states to calculate the percentage of charged off loans in the other states.

#Create 2 dataframes, one for total, one for charged off.
#Merge them to get the common state records
st_df = pd.DataFrame({'state':addr_state_total.index, 'loan_count':addr_state_total.values})
st_npaid_df = pd.DataFrame({'state':addr_state_npaid.index, 'loan_npaid_count':addr_state_npaid.values})
merged_st_df = pd.merge(st_df, st_npaid_df, on='state', how='inner')

merged_st_df.describe()

	loan_count	loan_npaid_count
count	45.000000	45.000000
mean	788.355556	111.533333
std	1124.925536	169.040958
min	10.000000	1.000000
25%	163.000000	24.000000
50%	399.000000	60.000000
75%	955.000000	139.000000
max	6419.000000	993.000000

Insight:

• We see that the loan_count is right skewed (mean >> std), this means we have more number of loans that are more than 788.
• It will be helpful to exclude states that have taken very few loans in order to have an unbiased anaysis. We take the top 75% states and perform analysis.

merged_st_df = merged_st_df.nlargest(35, 'loan_count')

# Plot a Graph to Display Percentage of Charged Off Loans Across States

addr_state_npaid_per = ( merged_st_df.loan_npaid_count / merged_st_df.loan_count ) *100

fig = go.Figure()
fig.add_trace(go.Bar(
    x=merged_st_df.state,
    y=addr_state_npaid_per,
    name='State',
    marker=dict(color='Blue')
))
bar_width = 0.2
fig.update_traces(width=bar_width)
fig.update_layout(
    title='Variation in Charged off Loans Across States',
    xaxis_title='State',
    yaxis_title='Percentage of Charged Off Loans'
)
fig.show()

Insight:

• NV, FL, NM default more.
• State of issue of loan does not have a considerable impact on the percentage of charged off loans

Q.DTI

• A ratio calculated using the borrower’s total monthly debt payments on the total debt obligations, excluding mortgage and the requested LC loan, divided by the borrower’s self-reported monthly income
• Higher dti corresponds to high risk loans.

# Plot Graph to Display Spread of DTI Over Loan Status, understand outliers
fig = go.Figure(data=go.Box(x=loan.loan_status, y=loan.dti))
fig.update_layout(title='Spread of DTI Across Various Loan Status', 
                  yaxis_title='DTI',
                  xaxis_title='Loan Status',
                  height = 1000,
                  width = 700
                  )
fig.show()

Insight:

• Charged Off Loans are right skewed; i.e., higher the DTI, higher the number of charged off loans with a median dti of 14.41.

# Plot a Histogram to Display Spread of DTI Over Loan Status

fig = go.Figure(data=go.Histogram(x=loan.dti))
fig.update_layout(title='Distribution of DTI', 
                  xaxis_title='DTI', 
                  yaxis_title='Frequency',
                  height = 700,
                  xaxis=dict(showgrid=True),
                  yaxis=dict(showgrid=True))

fig.show()

Insight:

• Similar to the box plot, we can see that more number of loans are obtained for a DTI between 12 and 15.
• DTI values range from 0 to 30.

# Plot a Graph to Display Percentage of Charged Off Loans Over DTI.
# Since DTI is a Numeric Variable, we will bucket it first.

loan['dti_bin'] = round(loan.dti)
loan_npaid = loan[loan.loan_status =='Charged Off']

dti_npaid = loan_npaid.dti_bin.value_counts().sort_index()
dti_total = loan.dti_bin.value_counts().sort_index()
dti_npaid_per = (dti_npaid.values / dti_total.values)*100

fig = go.Figure()
fig.add_trace(go.Bar(
    x=dti_total.index,
    y=dti_npaid_per,
    name='DTI',
    marker=dict(color='Blue')
))
bar_width = 0.2
fig.update_traces(width=bar_width)
fig.update_layout(
    title='Variation in Debtors Over DTI',
    xaxis_title='DTI',
    yaxis_title='Percentage of Charged Off Loans',
)
fig.show()

Insight:

• Over 15% loans are being charged off for dti over 15(high risk loans) which is higher than our set threshold value for analysis.
• A downward curve is seen for lower dti and an upward curve is visible for higher dti which confirms that at high dti, risk of charging off is more.

R. Public Record Bankruptcies

• Number of public record of previous bankruptcies
• numeric (0,1,2)

loan.pub_rec_bankruptcies.value_counts()

0.0    34006
1.0     1471
2.0        5
Name: pub_rec_bankruptcies, dtype: int64

loan['pub_rec_bankruptcies_str'] = loan.pub_rec_bankruptcies.astype('str')
loan_npaid = loan[loan.loan_status =='Charged Off']

# Plot a Graph to Display the Number of Loans Taken if Bankruptcy Record Exists

pub_rec_bankruptcies_total = (loan.pub_rec_bankruptcies_str.value_counts()).sort_index()

fig = go.Figure()
fig.add_trace(go.Bar(
    x=pub_rec_bankruptcies_total.index,
    y=pub_rec_bankruptcies_total.values,
    name='Record of Bankruptcies',
    marker=dict(color='Blue')
))
bar_width = 0.2
fig.update_traces(width=bar_width)
fig.update_layout(
    title='Number of Loans Taken if Bankruptcy Record Exists',
    xaxis_title='Record of Bankruptcies',
    yaxis_title='Number of Loans'
)
fig.show()

Insight:

• Borrowers with no record of bankruptcy are given more loans.
• The bar chart is not very clear but the value counts above shows that there are 5 records of borrowers with 2 bankruptcy records.

# Plot a Graph to Display the Percentage of Charged Off Loans if Bankruptcy Record Exists

pub_rec_bankruptcies_npaid = loan_npaid.pub_rec_bankruptcies_str.value_counts().sort_index()
pub_rec_bankruptcies_npaid_per = (pub_rec_bankruptcies_npaid.values / pub_rec_bankruptcies_total.values)*100

fig = go.Figure()
fig.add_trace(go.Bar(
    x=pub_rec_bankruptcies_total.index,
    y=pub_rec_bankruptcies_npaid_per,
    name='Charged Off Loans',
    marker=dict(color='Blue')
))
bar_width = 0.2
fig.update_traces(width=bar_width)
fig.update_layout(
    title='Variation in Charged off Loans if Bankruptcy Record Exists',
    xaxis_title='Bankruptcy Record Exists',
    yaxis_title='Percentage of Charged Off Loans'
)
fig.show()

Insight:

• People with prior record of bankruptcy tend to charge off again

5. Bivariate Analysis

Correlation Matrix for Numeric Variables

res = loan_npaid.corr(numeric_only = True)
res.index = res.index.astype(str)
fig = go.Figure(data=go.Heatmap(x=res.columns, y=res.index, z=res.values, colorscale='Blues'))

fig.update_layout(
    title='Correlation between different numeric values')
# Display the chart
fig.show()

Insight:

• Loan amount, funded amount, funded amount invested, installment are highly correlated.
• Therefore, we can consider only one of these variables in our multivarate analysis with other metrics.

Loan Amount Vs Loan Term

# Plot Graph to Display Spread of Loan Amount Over Loan Term
fig = go.Figure(data=go.Box(x=loan_npaid.term, y=loan_npaid.loan_amnt))
fig.update_layout(title='Spread of Loan Amount Across Various Loan Terms', 
                  yaxis_title='Loan Amount',
                  xaxis=dict(title='Loan Term', categoryorder='category ascending'),
                  height = 1000,
                  width = 700
                  )
fig.show()

loan_count = loan.groupby(['term', 'loan_amnt_bin', 'loan_status']).size().unstack()
loan_count = loan_count.reset_index()
loan_count['npaid_ratio'] = round((loan_count['Charged Off'] / (loan_count['Charged Off'] + loan_count['Fully Paid']))*100,2)
pvt = pd.pivot_table(data=loan_count, index="loan_amnt_bin", columns="term", values="npaid_ratio")
fig = go.Figure()

fig.add_trace(go.Scatter(
    x=loan_amnt_cat_arr,
    y=pvt[' 36 months'],
    mode='lines',
    name='36 months')
)

fig.add_trace(go.Scatter(
    x=loan_amnt_cat_arr,
    y=pvt[' 60 months'],
    mode='lines',
    name='60 months')
)

fig.update_layout(
    title='Spread of Charged Off Loan Amounts Over Loan Term',
    xaxis=dict(title='Loan Amount',categoryorder='array', categoryarray=loan_amnt_cat_arr),
    yaxis=dict(title='Percentage of Charged Off Loans')
)

fig.show()

Insight:

• From univariate analysis, we know that 60 month loans are charged off more. This graph further stresses the same point.
• The graph shows that long term loans of larger loan amounts (>15000) charge off over 25% which is a cause for concern.

Loan Amount Vs Interest Rate

# Create a scatter plot
fig = go.Figure(data=go.Scatter(x=loan_npaid.int_rate, y=loan_npaid.loan_amnt, mode='markers'))

# Update layout
fig.update_layout(
    title='Spread of Loan Amount Over Interest Rate',
    xaxis=dict(title='Interest Rate'),
    yaxis=dict(title='Loan Amount')
)

# Show the plot
fig.show()

loan_count = loan.groupby(['loan_amnt_bin_str', 'int_rate_bin', 'loan_status']).size().unstack()
loan_count = loan_count.reset_index()
loan_count['npaid_ratio'] = round((loan_count['Charged Off'] / (loan_count['Charged Off'] + loan_count['Fully Paid']))*100,2)
pvt = pd.pivot_table(data=loan_count, index="loan_amnt_bin_str", columns="int_rate_bin", values="npaid_ratio")

row_labels = pvt.index.get_level_values("loan_amnt_bin_str")
column_labels = pvt.columns.get_level_values("int_rate_bin")

# Create the heatmap trace
heatmap = go.Heatmap(
    x=column_labels,
    y=row_labels,
    z=pvt.values,
    colorscale='Blues' 
)

layout = go.Layout(
    title="Spread of Charged Off Loan Amounts Over Interest Rate",
    xaxis=dict(title="Interest Rate"),
    yaxis=dict(title='Percentage of Charged Off Loan Amounts',categoryorder='array', categoryarray=loan_amnt_cat_arr)
)

fig = go.Figure(data=[heatmap], layout=layout)

fig.show()

Insight:

• The number of loans with with interest rate greater than 15% is charging off more across all loan amount bins.

Loan Amnt Vs Installment Amnt

# Create a scatter plot
fig = go.Figure(data=go.Scatter(x=loan_npaid.installment, y=loan_npaid.loan_amnt, mode='markers'))

# Update layout
fig.update_layout(
    title='Distribution of Loan Amount Vs Installment Amounts',
    xaxis=dict(title='Installment Amount'),
    yaxis=dict(title='Loan Amount')
)

# Show the plot
fig.show()

Insight:

Larger loan amounts have larger installment amounts, highly correlated. So installment amount can be left out for further analysis.

Loan Amount Vs Grade

fig = go.Figure(data=go.Box(x=loan_npaid.grade, y=loan_npaid.loan_amnt))
fig.update_layout(title='Spread of Loan Amount Across Various Grades', 
                  yaxis_title='Loan Amount',
                  xaxis=dict(title='Loan Grade', categoryorder='category ascending'),
                  height = 1000,
                  width = 700
                  )
fig.show()

loan_count = loan.groupby(['loan_amnt_bin_str', 'grade', 'loan_status']).size().unstack()
loan_count = loan_count.reset_index()
loan_count['npaid_ratio'] = round((loan_count['Charged Off'] / (loan_count['Charged Off'] + loan_count['Fully Paid']))*100,2)

pvt = pd.pivot_table(data=loan_count, index="loan_amnt_bin_str", columns="grade", values="npaid_ratio")

row_labels = pvt.index.get_level_values("loan_amnt_bin_str")
column_labels = pvt.columns.get_level_values("grade")

# Create the heatmap trace
heatmap = go.Heatmap(
    x=column_labels,
    y=row_labels,
    z=pvt.values,
    colorscale='Blues'  
)
layout = go.Layout(
    title="Spread of Charged Off Loan Amounts Over Loan Grades",
    xaxis=dict(title="Grades"),
    yaxis=dict(title='Percentage of Charged Off Loan Amounts',categoryorder='array', categoryarray=loan_amnt_cat_arr)
)
fig = go.Figure(data=[heatmap], layout=layout)
fig.show()

Insight:

Higher loan amounts at e,f,g charge-off more. Confirms univariate for both the variables

Loan Amount Vs Sub Grade

fig = go.Figure(data=go.Box(x=loan_npaid.sub_grade, y=loan_npaid.loan_amnt))
fig.update_layout(title='Spread of Loan Amount Across Various Sub Grades', 
                  yaxis_title='Loan Amount',
                  xaxis=dict(title='Loan Sub Grade', categoryorder='category ascending'),
                  height = 1000,
                  width = 700
                  )
fig.show()

loan_count = loan.groupby(['loan_amnt_bin_str', 'sub_grade', 'loan_status']).size().unstack()
loan_count = loan_count.reset_index()
loan_count['npaid_ratio'] = round((loan_count['Charged Off'] / (loan_count['Charged Off'] + loan_count['Fully Paid']))*100,2)

pvt = pd.pivot_table(data=loan_count, index="loan_amnt_bin_str", columns="sub_grade", values="npaid_ratio")

row_labels = pvt.index.get_level_values("loan_amnt_bin_str")
column_labels = pvt.columns.get_level_values("sub_grade")

# Create the heatmap trace
heatmap = go.Heatmap(
    x=column_labels,
    y=row_labels,
    z=pvt.values,
    colorscale='Blues'  
)
layout = go.Layout(
    title="Spread of Charged Off Loan Amounts Over Loan Sub Grades",
    xaxis=dict(title="Sub Grades"),
    yaxis=dict(title='Percentage of Charged Off Loan Amounts',categoryorder='array', categoryarray=loan_amnt_cat_arr)
)
fig = go.Figure(data=[heatmap], layout=layout)
fig.show()

Insight:

• For smaller loan amounts, charge off happens at higher risk grades.
• As loan amount increases, charge off is happening starting from relatively lower risk grade.
• Across all loan amounts, however, charge off at high risk grades (F, G) is more.

Loan Amount Vs Home Ownership

fig = go.Figure(data=go.Box(x=loan_npaid.home_ownership, y=loan_npaid.loan_amnt))
fig.update_layout(title='Spread of Loan Amount Across Various Home Ownership Categories', 
                  yaxis_title='Loan Amount',
                  xaxis=dict(title='Home Ownership Category', categoryorder='category ascending'),
                  height = 1000,
                  width = 700
                  )
fig.show()

loan_count = loan.groupby(['home_ownership', 'loan_amnt_bin', 'loan_status']).size().unstack()
loan_count = loan_count.reset_index()
loan_count['npaid_ratio'] = round((loan_count['Charged Off'] / (loan_count['Charged Off'] + loan_count['Fully Paid']))*100,2)
pvt = pd.pivot_table(data=loan_count, index="loan_amnt_bin", columns="home_ownership", values="npaid_ratio")
fig = go.Figure()

fig.add_trace(go.Scatter(
    x=loan_amnt_cat_arr,
    y=pvt['MORTGAGE'],
    mode='lines',
    name='Mortgage')
)

fig.add_trace(go.Scatter(
    x=loan_amnt_cat_arr,
    y=pvt['OWN'],
    mode='lines',
    name='Own')
)

fig.add_trace(go.Scatter(
    x=loan_amnt_cat_arr,
    y=pvt['RENT'],
    mode='lines',
    name='Rent')
)

fig.update_layout(
    title='Spread of Charged Off Loan Amounts Over Home Onwership category',
    xaxis=dict(title='Loan Amount',categoryorder='array', categoryarray=loan_amnt_cat_arr),
    yaxis=dict(title='Percentage of Charged Off Loans')
)

fig.show()

Insight:

• Home Ownership Category has no impact on loan amount.

Loan Amount Vs Annual Income

# Create a scatter plot
fig = go.Figure(data=go.Scatter(x=loan_npaid.annual_inc[loan_npaid.annual_inc < 140000], 
                                y=loan_npaid.loan_amnt, mode='markers'))

# Update layout
fig.update_layout(
    title='Distribution of Loan Amount Vs Annual Income',
    xaxis=dict(title='Annual Income'),
    yaxis=dict(title='Loan Amount')
)

# Show the plot
fig.show()

loan_annual_inc = loan[loan.annual_inc <= 140000]
loan_count = loan_annual_inc.groupby(['loan_amnt_bin_str', 'annual_inc_bin_str', 'loan_status']).size().unstack()
loan_count = loan_count.reset_index()
loan_count['npaid_ratio'] = round((loan_count['Charged Off'] / (loan_count['Charged Off'] + loan_count['Fully Paid']))*100,2)
pvt = pd.pivot_table(data=loan_count, index="loan_amnt_bin_str", columns="annual_inc_bin_str", values="npaid_ratio")

row_labels = pvt.index.get_level_values("loan_amnt_bin_str")
column_labels = pvt.columns.get_level_values("annual_inc_bin_str")

# Create the heatmap trace
heatmap = go.Heatmap(
    x=column_labels,
    y=row_labels,
    z=pvt.values,
    colorscale='Blues' 
)

layout = go.Layout(
    title="Spread of Charged Off Loan Amounts Over Annual Income",
    xaxis=dict(title="Annual Income",categoryorder='array', categoryarray=annual_inc_cat_arr),
    yaxis=dict(title='Percentage of Charged Off Loan Amounts',categoryorder='array', categoryarray=loan_amnt_cat_arr)
)

fig = go.Figure(data=[heatmap], layout=layout)

fig.show()

Insight:

People with annual income from 20k to 60k taking loan more than 15k are charging off more.

Loan Amount Vs Month of Issue

fig = go.Figure(data=go.Box(x=loan.issue_month, y=loan.loan_amnt))
fig.update_layout(title='Spread of Loan Amount Across All Months', 
                  yaxis_title='Loan Amount',
                  xaxis=dict(title='Months', categoryorder='array', categoryarray=issue_month_cat_arr),
                  height = 1000,
                  width = 700
                  )
fig.show()

Insight:

• Loan amounts given across all months are evenly spread. There is a slight increase in the amount for December.
• This analysis, however, does not give us insight into how it affects the charge off. Therefore, month of issue is not a metric that affects charge off percentage.

Loan Amount Vs Loan Purpose

fig = go.Figure(data=go.Box(x=loan_npaid.purpose, y=loan_npaid.loan_amnt))
fig.update_layout(title='Spread of Loan Amount Over Loan Purpose', 
                  yaxis_title='Loan Amount',
                  xaxis=dict(title='Loan Purpose', categoryorder='category ascending'),
                  height = 1000,
                  width = 700
                  )
fig.show()

loan_count = loan.groupby(['loan_amnt_bin_str', 'purpose', 'loan_status']).size().unstack()
loan_count = loan_count.reset_index()
loan_count['npaid_ratio'] = round((loan_count['Charged Off'] / (loan_count['Charged Off'] + loan_count['Fully Paid']))*100,2)

pvt = pd.pivot_table(data=loan_count, index="loan_amnt_bin_str", columns="purpose", values="npaid_ratio")


# Extract the row and column labels
row_labels = pvt.index.get_level_values("loan_amnt_bin_str")
column_labels = pvt.columns.get_level_values("purpose")

# Create the heatmap trace
heatmap = go.Heatmap(
    x=column_labels,
    y=row_labels,
    z=pvt.values,
    colorscale='Blues'  # You can choose any colorscale you prefer
)

# Create the layout
layout = go.Layout(
    title="Spread of Charged Off Loan Amounts Over Loan Purpose",
    xaxis=dict(title="Purpose"),
    yaxis=dict(title="Percentage of Charged Off Loans",categoryorder='array', categoryarray=loan_amnt_cat_arr)
)

# Create the figure
fig = go.Figure(data=[heatmap], layout=layout)

# Display the plot
fig.show()

Insight:

• From univariate analysis, we know small businesses charge off at a higher percentage. Here, we can see that insight being confirmed with the darker blues.
• In small businesses, loan amounts from 10k to 35k charge off more, especially the higher loan amounts of 25k and above.
• It is interesting to see that the loan amounts between 20k to 25k have a higher charge off percentage across loan purposes.
• A crucial insight here is that higher loan amounts (>25k) are charging off at a significant rate when taken for medical purposes. One can think that it could be due to increased death rates for crucial ailments.

Loan Amount Vs State

fig = go.Figure(data=go.Box(x=loan.addr_state, y=loan.loan_amnt))
fig.update_layout(title='Spread of Loan Amount Across States', 
                  yaxis_title='Loan Amount',
                  xaxis=dict(title='States', categoryorder='category ascending'),
                  height = 1000,
                  width = 700
                  )
fig.show()

Insight:

• Very few people take loans greater than 30k in all states as per the outliers in the above graph.

loan_state = loan[loan['addr_state'].isin(merged_st_df['state'])]
loan_count = loan_state.groupby(['loan_amnt_bin_str', 'addr_state', 'loan_status']).size().unstack()
loan_count = loan_count.reset_index()
loan_count['npaid_ratio'] = round((loan_count['Charged Off'] / (loan_count['Charged Off'] + loan_count['Fully Paid']))*100,2)

pvt = pd.pivot_table(data=loan_count, index="loan_amnt_bin_str", columns="addr_state", values="npaid_ratio")


# Extract the row and column labels
row_labels = pvt.index.get_level_values("loan_amnt_bin_str")
column_labels = pvt.columns.get_level_values("addr_state")

# Create the heatmap trace
heatmap = go.Heatmap(
    x=column_labels,
    y=row_labels,
    z=pvt.values,
    colorscale='Blues'  # You can choose any colorscale you prefer
)

# Create the layout
layout = go.Layout(
    title="Spread of Charged Off Loan Amounts Across States",
    xaxis=dict(title="State"),
    yaxis=dict(title="Percentage of Charged Off Loans",categoryorder='array', categoryarray=loan_amnt_cat_arr)
)

# Create the figure
fig = go.Figure(data=[heatmap], layout=layout)

# Display the plot
fig.show()

Insight:

• We know that most loans are given out in CA, NY as per univariate analysis.
• Most charge off happens at NV, FL, NM as per univariate analysis.
• Let's concentrate on these states here.
• CA has more charge off for higher loans amounts (> 20k)
• It is clear that NV is charging off across all loan amounts. Loans amounts for this state have to be given out carefully.
• NY pays off loans well.
• In general, across states, charge off is high for higher loan amounts (>25k)

Loan Amount Vs DTI

# Create a scatter plot
fig = go.Figure(data=go.Scatter(x=loan.dti, y=loan.loan_amnt, mode='markers'))

# Update layout
fig.update_layout(
    title='Distribution of Loan Amount Across DTI',
    xaxis=dict(title='DTI'),
    yaxis=dict(title='Loan Amount')
)

# Show the plot
fig.show()

Insight:

• Lending Club gives out lesser loans at higher DTI.= which is good.
• Loan density is high between 10 to 25 dti.
• Smaller loan amounts (<10k) are given out irrespective of the dti.

loan_count = loan.groupby(['loan_amnt_bin_str', 'dti_bin', 'loan_status']).size().unstack()
loan_count = loan_count.reset_index()
loan_count['npaid_ratio'] = round((loan_count['Charged Off'] / (loan_count['Charged Off'] + loan_count['Fully Paid']))*100,2)
pvt = pd.pivot_table(data=loan_count, index="loan_amnt_bin_str", columns="dti_bin", values="npaid_ratio")

row_labels = pvt.index.get_level_values("loan_amnt_bin_str")
column_labels = pvt.columns.get_level_values("dti_bin")

# Create the heatmap trace
heatmap = go.Heatmap(
    x=column_labels,
    y=row_labels,
    z=pvt.values,
    colorscale='Blues' 
)

layout = go.Layout(
    title="Spread of Charged Off Loan Amounts Over DTI",
    xaxis=dict(title="DTI"),
    yaxis=dict(title='Percentage of Charged Off Loan Amounts',categoryorder='array', categoryarray=loan_amnt_cat_arr)
)

fig = go.Figure(data=[heatmap], layout=layout)

fig.show()

Insight:

• The above two graphs show that Lending Club is giving out loans at the right dti.
• Most loans are charged off for higher loan amounts(>20k) for high dti(>16).

Loan Amount Vs Bankruptices

fig = go.Figure(data=go.Box(x=loan.pub_rec_bankruptcies, y=loan.loan_amnt))
fig.update_layout(title='Spread of Loan Amount Across States', 
                  yaxis_title='Loan Amount',
                  xaxis=dict(title='Bankruptcies', categoryorder='category ascending'),
                  height = 1000,
                  width = 700
                  )
fig.show()

loan_count = loan.groupby(['pub_rec_bankruptcies_str', 'loan_amnt_bin', 'loan_status']).size().unstack()
loan_count = loan_count.reset_index()
loan_count['npaid_ratio'] = round((loan_count['Charged Off'] / (loan_count['Charged Off'] + loan_count['Fully Paid']))*100,2)
pvt = pd.pivot_table(data=loan_count, index="loan_amnt_bin", columns="pub_rec_bankruptcies_str", values="npaid_ratio")
fig = go.Figure()

fig.add_trace(go.Scatter(
    x=loan_amnt_cat_arr,
    y=pvt['0.0'],
    mode='lines',
    name='0')
)

fig.add_trace(go.Scatter(
    x=loan_amnt_cat_arr,
    y=pvt['1.0'],
    mode='lines',
    name='1')
)

fig.add_trace(go.Scatter(
    x=loan_amnt_cat_arr,
    y=pvt['2.0'],
    mode='lines',
    name='2')
)

fig.update_layout(
    title='Spread of Charged Off Loan Amounts Over Bankruptcy Record',
    xaxis=dict(title='Loan Amount',categoryorder='array', categoryarray=loan_amnt_cat_arr),
    yaxis=dict(title='Percentage of Charged Off Loans')
)

fig.show()

Insight:

• It is definitely good to avoid loans for borrowers with previous bankruptcy records.
• Lending club is already giving out very meagre number of loans to people with prior bankruptcy history.

Bivariate Analysis on Loan Term With Other Metrics

Loan Term Vs Interest Rate

fig = go.Figure(data=go.Violin(x=loan.term, y=loan.int_rate, box_visible=True, meanline_visible=True))

fig.update_layout(title='Spread of Interest Rates For Loan Terms', 
                  yaxis_title='Interest Rate',
                  xaxis=dict(title='Loan Term', categoryorder='category ascending'),
                  height = 1000,
                  width = 700
                  )
fig.show()

Insight:

• Short term loans are given at relatively lesser interest rates and long term loans are given at higher interest rates.
• This also explains why long term loans charge off more - we know higher interest rate loans charge off more.

loan_count = loan.groupby(['term', 'int_rate_bin', 'loan_status']).size().unstack()
loan_count = loan_count.reset_index()
loan_count['npaid_ratio'] = round((loan_count['Charged Off'] / (loan_count['Charged Off'] + loan_count['Fully Paid']))*100,2)
pvt = pd.pivot_table(data=loan_count, index="int_rate_bin", columns="term", values="npaid_ratio")
fig = go.Figure()

fig.add_trace(go.Scatter(
    x=pvt.index,
    y=pvt[' 36 months'],
    mode='lines',
    name='36 months')
)

fig.add_trace(go.Scatter(
    x=pvt.index,
    y=pvt[' 60 months'],
    mode='lines',
    name='60 months')
)

fig.update_layout(
    title='Spread of Interest Rates Over Loan Term',
    xaxis=dict(title='Interest Rate'),
    yaxis=dict(title='Percentage of Charged Off Loans')
)

fig.show()

Insight;

• It is clear that long term loans have a higher charge off rate as interest rate increases.
• But after int rate >20, the gap between both loan terms reduces.

Loan Term Vs Sub-Grades

loan_count = loan.groupby(['sub_grade', 'term', 'loan_status']).size().unstack()
loan_count = loan_count.reset_index()
loan_count['npaid_ratio'] = round((loan_count['Charged Off'] / (loan_count['Charged Off'] + loan_count['Fully Paid']))*100,2)
pvt = pd.pivot_table(data=loan_count, index="sub_grade", columns="term", values="npaid_ratio")

# Plot a graph
fig = go.Figure()

row_labels = pvt.index.get_level_values("sub_grade")
column_labels = pvt.columns.get_level_values("term")
z=pvt.values

fig.add_trace(go.Bar(
    x=pvt.index,
    y=pvt[' 36 months'],
    name='36 months',
    offset=-0.2,
    marker=dict(color='Blue')
))
fig.add_trace(go.Bar(
    x=pvt.index,
    y=pvt[' 60 months'],
    name='60 months',
    offset=0.2,
    marker=dict(color='Green')
))
bar_width = 0.4
fig.update_traces(width=bar_width)
fig.update_layout(
    title='Loan Sub-Grades In Each Term',
    xaxis_title='Sub-Grade',
    yaxis_title='Ratio of Charged Off Loans',
    barmode='group'
)
fig.show()

Insight:

• Long term loans across all sub grades are charging off at a higher percentage.
• Interesting to note that for G2, G4 and G5, short term loans are charging off more. Further analysis for these grades is done next.

loan.int_rate[(loan.term == ' 36 months') & (loan.sub_grade.isin(['G2','G4''G5'])) & (loan.loan_status == 'Charged Off')]

13994    22.48
17986    20.48
22900    20.03
35072    20.17
36163    19.16
Name: int_rate, dtype: float64

Insight:

• High risk loans have high interest rate. Irrespective of the term, high risk loans (Grade G) charge off more.

Loan Term Vs Home Ownership

loan_home = loan[loan.home_ownership != 'OTHER']
loan_count = loan_home.groupby(['home_ownership', 'term', 'loan_status']).size().unstack()
loan_count = loan_count.reset_index()
loan_count['npaid_ratio'] = round((loan_count['Charged Off'] / (loan_count['Charged Off'] + loan_count['Fully Paid']))*100,2)
pvt = pd.pivot_table(data=loan_count, index="home_ownership", columns="term", values="npaid_ratio")

# Plot a graph
fig = go.Figure()

row_labels = pvt.index.get_level_values("home_ownership")
column_labels = pvt.columns.get_level_values("term")
z=pvt.values

fig.add_trace(go.Bar(
    x=pvt.index,
    y=pvt[' 36 months'],
    name='36 months',
    offset=-0.2,
    marker=dict(color='Blue')
))
fig.add_trace(go.Bar(
    x=pvt.index,
    y=pvt[' 60 months'],
    name='60 months',
    offset=0.2,
    marker=dict(color='Green')
))
bar_width = 0.4
fig.update_traces(width=bar_width)
fig.update_layout(
    title='Home Ownership In Each Term',
    xaxis_title='Home Ownership',
    yaxis_title='Ratio of Charged Off Loans',
    barmode='group'
)
fig.show()

Insight:

• We have removed 'other' category from analysis as suggested in Univariate analysis.
• We already know that long term loans are charged off more. So, home ownership does not impact loan term.

Loan Term Vs Annual Income

fig = go.Figure(data=go.Violin(x=loan.term, y=loan.annual_inc[loan.annual_inc < 140000], box_visible=True, meanline_visible=True))

fig.update_layout(title='Spread of Annual Income Over Loan Term', 
                  yaxis_title='Annual Income',
                  xaxis=dict(title='Loan Term', categoryorder='category ascending'),
                  height = 1000,
                  width = 700
                  )
fig.show()

Insight:

• People with income in all brackets take loan across both terms.

loan_count = loan_annual_inc.groupby(['term', 'annual_inc_bin_str', 'loan_status']).size().unstack()
loan_count = loan_count.reset_index()
loan_count['npaid_ratio'] = round((loan_count['Charged Off'] / (loan_count['Charged Off'] + loan_count['Fully Paid']))*100,2)
pvt = pd.pivot_table(data=loan_count, index="annual_inc_bin_str", columns="term", values="npaid_ratio")
fig = go.Figure()

fig.add_trace(go.Scatter(
    x=annual_inc_cat_arr,
    y=pvt[' 36 months'],
    mode='lines',
    name='36 months')
)

fig.add_trace(go.Scatter(
    x=annual_inc_cat_arr,
    y=pvt[' 60 months'],
    mode='lines',
    name='60 months')
)

fig.update_layout(
    title='Spread of Annual Income Over Loan Term',
    xaxis=dict(title='Annual Income', categoryorder='array', categoryarray=annual_inc_cat_arr),
    yaxis=dict(title='Percentage of Charged Off Loans')
)

fig.show()

Insight:

• People with income < 20k have a higher charge off percentage.
• Then there is a dip in charge off percentage and then after 50k, the charge off percentage increases until 90k annual income.
• Overall, long term loans are charged off at a higher percentage when compared to short term loans.

Loan Term Vs Loan Purpose

loan_count = loan.groupby(['purpose', 'term', 'loan_status']).size().unstack()
loan_count = loan_count.reset_index()
loan_count['npaid_ratio'] = round((loan_count['Charged Off'] / (loan_count['Charged Off'] + loan_count['Fully Paid']))*100,2)
pvt = pd.pivot_table(data=loan_count, index="purpose", columns="term", values="npaid_ratio")

# Plot a graph
fig = go.Figure()

row_labels = pvt.index.get_level_values("purpose")
column_labels = pvt.columns.get_level_values("term")
z=pvt.values

fig.add_trace(go.Bar(
    x=pvt.index,
    y=pvt[' 36 months'],
    name='36 months',
    offset=-0.2,
    marker=dict(color='Blue')
))
fig.add_trace(go.Bar(
    x=pvt.index,
    y=pvt[' 60 months'],
    name='60 months',
    offset=0.2,
    marker=dict(color='Green')
))
bar_width = 0.4
fig.update_traces(width=bar_width)
fig.update_layout(
    title='Purpose Vs Term',
    xaxis_title='Purpose',
    yaxis_title='Percentage of Charged Off Loans',
    barmode='group'
)
fig.show()

Insight:

• Loan term and purpose have a high impact on loans of being charged off as can be seen here.
• Credit card, debt consolidation, educational, house, major purchase, small business, vacation - for these purpose, long term loans charge off more than twice the percentage when compared to short term loans.

Loan Term Vs DTI

fig = go.Figure(data=go.Violin(x=loan.term, y=loan.dti, box_visible=True, meanline_visible=True))

fig.update_layout(title='Spread of DTI Over Loan Term', 
                  yaxis_title='DTI',
                  xaxis=dict(title='Loan Term', categoryorder='category ascending'),
                  height = 1000,
                  width = 700
                  )
fig.show()

Insight

• People with DTI in all ranges equally take loans in both short term and long term.
• We can now see how the two metrics behave with charged off loans

loan_count = loan.groupby(['term', 'dti_bin', 'loan_status']).size().unstack()
loan_count = loan_count.reset_index()
loan_count['npaid_ratio'] = round((loan_count['Charged Off'] / (loan_count['Charged Off'] + loan_count['Fully Paid']))*100,2)
pvt = pd.pivot_table(data=loan_count, index="dti_bin", columns="term", values="npaid_ratio")
fig = go.Figure()

fig.add_trace(go.Scatter(
    x=pvt.index,
    y=pvt[' 36 months'],
    mode='lines',
    name='36 months')
)

fig.add_trace(go.Scatter(
    x=pvt.index,
    y=pvt[' 60 months'],
    mode='lines',
    name='60 months')
)

fig.update_layout(
    title='Spread of DTI Over Loan Term',
    xaxis=dict(title='DTI'),
    yaxis=dict(title='Percentage of Charged Off Loans')
)

fig.show()

Insight:

• Long term loans charge off more, Long term loans strongly default more in comparison to short term ones.

Interest Rate Vs SubGrade

# See if interest rate and sub grade are related (for all loans, not just for charged off loans)
fig = go.Figure(data=go.Violin(x=loan.sub_grade, y=loan.int_rate, box_visible=True, meanline_visible=True))

fig.update_layout(title='Spread of Interest Rate Over Sub Grades', 
                  yaxis_title='Interest Rate',
                  xaxis=dict(title='Sub Grade', categoryorder='category ascending'),
                  height = 1000,
                  width = 1000
                  )
fig.show()

Insight:

• High risk loans are given at high interest rates.
• From univariate analysis of both the metrics individually, we know that high risk loans and high interest rate loans are charged off at a higher rate.
• The correlation between these two metrics is high.
• For further analysis, we can consider just one of these metrics and understand that any impact on interest rate indirectly impacts grades of loans.

loan_count = loan.groupby(['int_rate_bin', 'sub_grade', 'loan_status']).size().unstack()
loan_count = loan_count.reset_index()
loan_count['npaid_ratio'] = round((loan_count['Charged Off'] / (loan_count['Charged Off'] + loan_count['Fully Paid']))*100,2)

pvt = pd.pivot_table(data=loan_count, index="int_rate_bin", columns="sub_grade", values="npaid_ratio")

row_labels = pvt.index.get_level_values("int_rate_bin")
column_labels = pvt.columns.get_level_values("sub_grade")

# Create the heatmap trace
heatmap = go.Heatmap(
    x=column_labels,
    y=row_labels,
    z=pvt.values,
    colorscale='Blues'  
)
layout = go.Layout(
    title="Spread of Charged Off Loans at Various Interest Rates Over Loan Sub Grades",
    xaxis=dict(title="Sub Grades"),
    yaxis=dict(title='Percentage of Charged Off Loans at Various Interest Rates')
)
fig = go.Figure(data=[heatmap], layout=layout)
fig.show()

Insight:

• Interest Rate and Grades are highly correlated.
• Higher the risk of the loan (grade: a<b<c<d<e<f<g), hgiher the interest rate at which it is being charged off.
• We can consider just one of these variables for future analysis.

Interest Rate Vs Annual Income

# Create a scatter plot
fig = go.Figure(data=go.Scatter(x=loan.annual_inc[loan.annual_inc<140000], y=loan.int_rate, mode='markers'))

# Update layout
fig.update_layout(
    title='Distribution of Interest Rate Over Annual Income',
    xaxis=dict(title='Annual Income'),
    yaxis=dict(title='Interest Rate')
)

# Show the plot
fig.show()

Insight:

• Interest Rate and Annual Incomehave an even spread when loans are taken.
• As income increases, lesser loans are taken.

loan_count = loan_annual_inc.groupby(['annual_inc_bin_str', 'int_rate_bin', 'loan_status']).size().unstack()
loan_count = loan_count.reset_index()
loan_count['npaid_ratio'] = round((loan_count['Charged Off'] / (loan_count['Charged Off'] + loan_count['Fully Paid']))*100,2)
pvt = pd.pivot_table(data=loan_count, index="annual_inc_bin_str", columns="int_rate_bin", values="npaid_ratio")

row_labels = pvt.index.get_level_values("annual_inc_bin_str")
column_labels = pvt.columns.get_level_values("int_rate_bin")

# Create the heatmap trace
heatmap = go.Heatmap(
    x=column_labels,
    y=row_labels,
    z=pvt.values,
    colorscale='Blues' 
)

layout = go.Layout(
    title="Spread of Charged Off Loan Amounts in Various Annual Income Brackets Over Interest Rate",
    xaxis=dict(title="Interest Rate"),
    yaxis=dict(title='Percentage of Charged Off Loan Amounts in Annual Income Brackets',categoryorder='array', categoryarray=annual_inc_cat_arr)
)

fig = go.Figure(data=[heatmap], layout=layout)

fig.show()

Insight:

• Irrespective of annual income, at higher interest rate >20%, more default happens.
• For income upto 100000, loans at interest rate from 17% itself is charging off more.

Interest Rate Vs Loan Purpose

fig = go.Figure(data=go.Violin(x=loan.purpose, y=loan.int_rate, box_visible=True, meanline_visible=True))

fig.update_layout(title='Impact of Interest Rate Over Loan Purpose', 
                  yaxis_title='Interest Rate',
                  xaxis=dict(title='Loan Purpose', categoryorder='category ascending'),
                  height = 1000,
                  width = 1000
                  )
fig.show()

Insight:

• Educational loan amount are mostly given out in the range of 10 to 15% interest rate.
• Average of loans across all purpose is mostly 10-12%.
• Q3 for small business is at 15.7 which is higher than Q3 for other loan purposes. This could also be one reason why small business loans are being charged off more.

loan_count = loan.groupby(['int_rate_bin', 'purpose', 'loan_status']).size().unstack()
loan_count = loan_count.reset_index()
loan_count['npaid_ratio'] = round((loan_count['Charged Off'] / (loan_count['Charged Off'] + loan_count['Fully Paid']))*100,2)

pvt = pd.pivot_table(data=loan_count, index="int_rate_bin", columns="purpose", values="npaid_ratio")

row_labels = pvt.index.get_level_values("int_rate_bin")
column_labels = pvt.columns.get_level_values("purpose")

# Create the heatmap trace
heatmap = go.Heatmap(
    x=column_labels,
    y=row_labels,
    z=pvt.values,
    colorscale='Blues'  
)
layout = go.Layout(
    title="Spread of Charged Off Loans at Various Interest Rates Over Loan Purpose",
    xaxis=dict(title="Purpose"),
    yaxis=dict(title='Percentage of Charged Off Loans at Various Interest Rates')
)
fig = go.Figure(data=[heatmap], layout=layout)
fig.show()

Insight:

• High interest rate is a cause for charge off across all purposes.
• We clearly see that small business loans have a darker blue shade across a wider interest rate range indicating more number of small business loans being charged off.
• Educational loans at 17-18% have a higher charge off rate in comparison to other loans at this interest rate.
• Housing loans at 15-21% charge off more.
• Loans taken for moving purposes charge off at lower interest rates too.

Interest Rate Vs DTI

# Create a scatter plot
fig = go.Figure(data=go.Scatter(x=loan.dti, y=loan.int_rate, mode='markers'))

# Update layout
fig.update_layout(
    title='Distribution of Interest Rate Over DTI',
    xaxis=dict(title='DTI'),
    yaxis=dict(title='Interest Rate')
)

# Show the plot
fig.show()

Insight:

• Interest Rate and DTI are not highly correlated as can be seen in the graph.
• Number of loans taken at higher interest rates and higher dti irrespective of the other metric is very low which means risky loans are given out less.

loan_count = loan_annual_inc.groupby(['int_rate_bin', 'dti_bin', 'loan_status']).size().unstack()
loan_count = loan_count.reset_index()
loan_count['npaid_ratio'] = round((loan_count['Charged Off'] / (loan_count['Charged Off'] + loan_count['Fully Paid']))*100,2)
pvt = pd.pivot_table(data=loan_count, index="int_rate_bin", columns="dti_bin", values="npaid_ratio")

row_labels = pvt.index.get_level_values("int_rate_bin")
column_labels = pvt.columns.get_level_values("dti_bin")

# Create the heatmap trace
heatmap = go.Heatmap(
    x=column_labels,
    y=row_labels,
    z=pvt.values,
    colorscale='Blues' 
)

layout = go.Layout(
    title="Spread of Charged Off Loan Amounts at Various Interest Rates Over DTI",
    xaxis=dict(title="DTI"),
    yaxis=dict(title='Percentage of Charged Off Loan Amounts at Various Interest Rates', categoryorder="category ascending")
)

fig = go.Figure(data=[heatmap], layout=layout)

fig.show()

Insight:

• Loans at higher interest rate irrespective of dti are being charged off.
• Likewise, loans at higher dti irrespective of interest rate are being charged off more wherever given.

Interest Rate Vs Bankruptcies

fig = go.Figure(data=go.Violin(x=loan.pub_rec_bankruptcies_str, y=loan.int_rate, box_visible=True, meanline_visible=True))

fig.update_layout(title='Impact of Interest Rate Over Bankruptcies Record', 
                  yaxis_title='Interest Rate',
                  xaxis=dict(title='Bankruptcies Record', categoryorder='category ascending'),
                  height = 1000,
                  width = 1000
                  )
fig.show()

Insight:

• We know borrowers with more bankruptcies should be avoided loans in general. No more insights need to be derived here.

Annual Income Vs Purpose

fig = go.Figure(data=go.Violin(x=loan.purpose, y=loan.annual_inc[loan.annual_inc<140000], box_visible=True, meanline_visible=True))

fig.update_layout(title='Impact of Annual Income on Loan Purpose', 
                  yaxis_title='Annual Income',
                  xaxis=dict(title='Loan Purpose', categoryorder='category ascending'),
                  height = 1000,
                  width = 1000
                  )
fig.show()

Insight:

• Borrowers taken loans for various purpose across all income groups.
• More people in the income range above 80k take loans for small businnes and vacations.

loan_count = loan_annual_inc.groupby(['annual_inc_bin_str', 'purpose', 'loan_status']).size().unstack()
loan_count = loan_count.reset_index()
loan_count['npaid_ratio'] = round((loan_count['Charged Off'] / (loan_count['Charged Off'] + loan_count['Fully Paid']))*100,2)

pvt = pd.pivot_table(data=loan_count, index="annual_inc_bin_str", columns="purpose", values="npaid_ratio")

row_labels = pvt.index.get_level_values("annual_inc_bin_str")
column_labels = pvt.columns.get_level_values("purpose")

# Create the heatmap trace
heatmap = go.Heatmap(
    x=column_labels,
    y=row_labels,
    z=pvt.values,
    colorscale='Blues'  
)
layout = go.Layout(
    title="Spread of Charged Off Loans at Various Income Brackets Over Loan Purpose",
    xaxis=dict(title="Purpose"),
    yaxis=dict(title='Percentage of Charged Off Loans at Various Income Brackets', categoryorder='array', categoryarray=annual_inc_cat_arr)
)
fig = go.Figure(data=[heatmap], layout=layout)
fig.show()
                                                             

Insight:

• Annual income does not have any impact on the purpose of loan being charged off.
• However, it is clear that small business and renewable energy loans are being charged off at all income levels.

Annual Income Vs DTI

# Create a scatter plot
fig = go.Figure(data=go.Scatter(x=loan.annual_inc[loan.annual_inc<140000], y=loan.dti, mode='markers'))

# Update layout
fig.update_layout(
    title='Distribution of Annual Income Over DTI',
    xaxis=dict(title='Annual Income'),
    yaxis=dict(title='DTI')
)

# Show the plot
fig.show()

Insight:

• Most loans have been given off between the range of 20k and 100k annual income but the dti is well spread.
• Correlation between these two metrics is less.

loan_count = loan_annual_inc.groupby(['annual_inc_bin_str', 'dti_bin', 'loan_status']).size().unstack()
loan_count = loan_count.reset_index()
loan_count['npaid_ratio'] = round((loan_count['Charged Off'] / (loan_count['Charged Off'] + loan_count['Fully Paid']))*100,2)
pvt = pd.pivot_table(data=loan_count, index="annual_inc_bin_str", columns="dti_bin", values="npaid_ratio")

row_labels = pvt.index.get_level_values("annual_inc_bin_str")
column_labels = pvt.columns.get_level_values("dti_bin")

# Create the heatmap trace
heatmap = go.Heatmap(
    x=column_labels,
    y=row_labels,
    z=pvt.values,
    colorscale='Blues' 
)

layout = go.Layout(
    title="Spread of Charged Off Loan Amounts at Various Income Brackets Over DTI",
    xaxis=dict(title="DTI"),
    yaxis=dict(title='Percentage of Charged Off Loan Amounts at Various Income Brackets', categoryorder="array", categoryarray=annual_inc_cat_arr)
)

fig = go.Figure(data=[heatmap], layout=layout)

fig.show()

Insight:

• We see a slight increase in the blue shade for lower income levels as DTI increases.
• Upto an annual income of 40k with dti above 10 seems to have more charged off loan percentage.

Annual Income Vs Bankruptcies

fig = go.Figure(data=go.Violin(x=loan.pub_rec_bankruptcies_str, y=loan.annual_inc[loan.annual_inc<140000], box_visible=True, meanline_visible=True))

fig.update_layout(title='Impact of Annual Income on Bankruptcies Records', 
                  yaxis_title='Annual Income',
                  xaxis=dict(title='Banruptcies Records', categoryorder='category ascending'),
                  height = 1000,
                  width = 1000
                  )
fig.show()

Loan Purpose Vs DTI

fig = go.Figure(data=go.Violin(x=loan.purpose, y=loan.dti, box_visible=True, meanline_visible=True))

fig.update_layout(title='Impact of DTI on Loan Purpose', 
                  yaxis_title='DTI',
                  xaxis=dict(title='Loan Purpose', categoryorder='category ascending'),
                  height = 1000,
                  width = 1000
                  )
fig.show()

Insight:

• High risk loans are given for credit card and debt consolidation in comparison to the other loan purposes.

loan_count = loan_annual_inc.groupby(['dti_bin', 'purpose', 'loan_status']).size().unstack()
loan_count = loan_count.reset_index()
loan_count['npaid_ratio'] = round((loan_count['Charged Off'] / (loan_count['Charged Off'] + loan_count['Fully Paid']))*100,2)

pvt = pd.pivot_table(data=loan_count, index="dti_bin", columns="purpose", values="npaid_ratio")

row_labels = pvt.index.get_level_values("dti_bin")
column_labels = pvt.columns.get_level_values("purpose")

# Create the heatmap trace
heatmap = go.Heatmap(
    x=column_labels,
    y=row_labels,
    z=pvt.values,
    colorscale='Blues'  
)
layout = go.Layout(
    title="Spread of Charged Off Loans for Various DTIs Over Loan Purpose",
    xaxis=dict(title="Purpose"),
    yaxis=dict(title='Percentage of Charged Off Loans for Various DTIs', categoryorder='category ascending')
)
fig = go.Figure(data=[heatmap], layout=layout)
fig.show()
                                                             

Insight:

• Small business laons are being charged off at all DTIs.
• There is a clear indication that loans with higher DTIs are being charged off at a higher rate.
• Renewable sources of energy loans are charging off at a high rate across dtis.

Loan Purpose Vs Grade

loan_count = loan.groupby(['grade', 'purpose', 'loan_status']).size().unstack()
loan_count = loan_count.reset_index()
loan_count['npaid_ratio'] = round((loan_count['Charged Off'] / (loan_count['Charged Off'] + loan_count['Fully Paid']))*100,2)

pvt = pd.pivot_table(data=loan_count, index="grade", columns="purpose", values="npaid_ratio")

row_labels = pvt.index.get_level_values("grade")
column_labels = pvt.columns.get_level_values("purpose")

# Create the heatmap trace
heatmap = go.Heatmap(
    x=column_labels,
    y=row_labels,
    z=pvt.values,
    colorscale='Blues'  
)
layout = go.Layout(
    title="Spread of Charged Off Loans for Various Grades Over Loan Purpose",
    xaxis=dict(title="Purpose"),
    yaxis=dict(title='Percentage of Charged Off Loans for Various Grades', categoryorder='category ascending')
)
fig = go.Figure(data=[heatmap], layout=layout)
fig.show()
                 

Insight:

• This is similar to interest rate, at higher grades, charge off is more.

6. Multivariate Analysis

loan_an = loan_npaid[loan_npaid.annual_inc < 140000]
sns.pairplot(loan_an[['loan_amnt','term','int_rate','dti','annual_inc']])
plt.show()

Recommendation:

• Larger loan amounts (> 15000) for 60-month term across high risk grades (D to G) must be given with the caution.
• In general, small business loans are charging of at a high rate - these loans have to be given out with caution considering these additional variables - Loan amount taken between 10k to 35k, for 60 month term with interest rate > 9%.
• Higher loan amounts (>20k) having higher dti (>16) have to be given out with caution.
• Educational and housing loans taken at 60 month term with interest rate >15% have to be given out with caution.
• People with annual income between 20k to 60k taking loans more than 15k at higher interest rates (>17%) tend to have higher dti (>10 - these are high risk loans) have to be considered with caution.
• High risk loans with grades E,F,G charge off more than 30% and have to be given out with caution.
• Loans given out in NV state have to be considered with caution.
• Although we do not have sufficient data on borrowers with previous record of bankruptcies, from the available dataset, it can be seen that people with previous record of bankruptcies tend to default again. These loans have to be given with less priority.